Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

calculate sum for values in SQL for display per month name

i have a table with the following layout.

Email Blast Table

EmailBlastId |  FrequencyId | UserId
---------------------------------
1            |   5          |   1
2            |   2          |   1
3            |   4          |   1


Frequency Table

Id | Frequency 
------------
 1 |  Daily
 2 |  Weekly
 3 |  Monthly
 4 |  Quarterly
 5 |  Bi-weekly

I need to come up with a grid display on my asp.net page as follows.

Email blasts per month.

UserId | Jan | Feb | Mar | Apr |..... Dec | Cumulative
-----------------------------------------------------
1        7      6     6     7          6     #xx

The only way I can think of doing this is as below, for each month have a case statement.

select SUM(
        CASE WHEN FrequencyId = 1 THEN 31 
        WHEN FrequencyId = 2 THEN 4
        WHEN FrequencyId = 3 THEN 1
        WHEN FrequencyId = 4 THEN 1
        WHEN FrequencyId = 5 THEN 2 END) AS Jan, 
      SUM(
        CASE WHEN FrequencyId = 1 THEN 28 (29 - leap year)
        WHEN FrequencyId = 2 THEN 4
        WHEN FrequencyId = 3 THEN 1
        WHEN FrequencyId = 4 THEN 0
        WHEN FrequencyId = 5 THEN 2 END) AS Feb, etc etc
FROM EmailBlast 
Group BY UserId

Any other better way of achieving the same?

like image 637
Alex J Avatar asked Aug 25 '11 15:08

Alex J


People also ask

How do you calculate monthly sum in SQL?

If you only want a total count of sales every month, then you can use COUNT function instead. mysql> select year(order_date),month(order_date),sum(sale) from sales WHERE condition group by year(order_date),month(order_date) order by year(order_date),month(order_date);

How do you sum sales by month in SQL?

You can also find out the total count of sales every month. For that, replace the SUM function with the COUNT function. Query: SELECT YEAR(Order_date) AS Year,MONTH(Order_date) AS Month,COUNT(Sales) AS Count_Of_Sales FROM Products GROUP BY YEAR(Order_date),MONTH(Order_date);

How do I display a sum of records in SQL?

AVG() SyntaxThe SUM() function returns the total sum of a numeric column.


2 Answers

Is this for any given year? I'm going to assume you want the schedule for the current year. If you want a future year you can always change the DECLARE @now to specify any future date.

"Once in 2 weeks" (usually known as "bi-weekly") doesn't fit well into monthly buckets (except for February in a non-leap year). Should that possibly be changed to "Twice a month"?

Also, why not store the coefficient in the Frequency table, adding a column called "PerMonth"? Then you only have to deal with the Daily and Quarterly cases (and is it an arbitrary choice that this will happen only in January, April, and so on?).

Assuming that some of this is flexible, here is what I would suggest, assuming this very minor change to the table schema:

USE tempdb;
GO

CREATE TABLE dbo.Frequency 
(
    Id INT PRIMARY KEY,
    Frequency VARCHAR(32),
    PerMonth TINYINT
);

CREATE TABLE dbo.EmailBlast 
(
    Id INT,
    FrequencyId INT,
    UserId INT
);

And this sample data:

INSERT dbo.Frequency(Id, Frequency, PerMonth)
  SELECT 1, 'Daily', NULL
  UNION ALL SELECT 2, 'Weekly', 4
  UNION ALL SELECT 3, 'Monthly', 1
  UNION ALL SELECT 4, 'Quarterly', NULL
  UNION ALL SELECT 5, 'Twice a month', 2;

INSERT dbo.EmailBlast(Id, FrequencyId, UserId)
  SELECT 1, 5, 1
  UNION ALL SELECT 2, 2, 1
  UNION ALL SELECT 3, 4, 1;

We can accomplish this using a very complex query (but we don't have to hard-code those month numbers):

DECLARE @now DATE = CURRENT_TIMESTAMP;
DECLARE @Jan1 DATE = DATEADD(MONTH, 1-MONTH(@now), DATEADD(DAY, 1-DAY(@now), @now));

WITH n(m) AS 
(
    SELECT TOP 12 m = number
        FROM master.dbo.spt_values
        WHERE number > 0 GROUP BY number
),
months(MNum, MName, StartDate, NumDays) AS
(    SELECT m, mn = CONVERT(CHAR(3), DATENAME(MONTH, DATEADD(MONTH, m-1, @Jan1))),
        DATEADD(MONTH, m-1, @Jan1), 
        DATEDIFF(DAY, DATEADD(MONTH, m-1, @Jan1), DATEADD(MONTH, m, @Jan1))
    FROM n
),
grp AS
(
    SELECT UserId, MName, c = SUM (
        CASE x.Id WHEN 1 THEN NumDays
            WHEN 4 THEN CASE WHEN MNum % 3 = 1 THEN 1 ELSE 0 END
            ELSE x.PerMonth END )
    FROM months CROSS JOIN (SELECT e.UserId, f.* 
        FROM EmailBlast AS e 
        INNER JOIN Frequency AS f
        ON e.FrequencyId = f.Id) AS x
    GROUP BY UserId, MName
),
cumulative(UserId, total) AS
(
    SELECT UserId, SUM(c)
      FROM grp GROUP BY UserID
),
pivoted AS
(
    SELECT * FROM (SELECT UserId, c, MName FROM grp) AS grp 
    PIVOT(MAX(c) FOR MName IN (
        [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
    ) AS pvt
)
SELECT p.*, c.total 
    FROM pivoted AS p
    LEFT OUTER JOIN cumulative AS c
    ON p.UserId = c.UserId;

Results:

UserId  Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec total
1       7   6   6   7   6   6   7   6   6   7   6   6   76

Clean up:

DROP TABLE dbo.EmailBlast, dbo.Frequency;
GO

In fact the schema change I suggested doesn't really buy you much, it just saves you two additional CASE branches inside the grp CTE. Peanuts, overall.

like image 151
Aaron Bertrand Avatar answered Nov 14 '22 23:11

Aaron Bertrand


I think you're going to end up with a lot more complicated logic. Sure Jan has 31 days.. but Feb doesn't... and Feb changes depending on the year. Next, are email blasts sent even on weekends and holidays or are certain days skipped for various reasons... If that's the case then the number of business days for a given month changes each year.

Next the number of full weeks in a given month also changes year by year. What happens to those extra 4 half weeks? Do they go on the current or next month? What method are you using to determine that? For an example of how complicated this gets read: http://en.wikipedia.org/wiki/ISO_week_date Specifically the part where it talks about the first week, which actually has 9 different definitions.

I'm usually not one to say this, but you might be better off writing this with regular code instead of a sql query. Just issue a 'select * from emailblast where userid = xxx' and transform it using a variety of code methods.

like image 22
NotMe Avatar answered Nov 14 '22 23:11

NotMe