Subqueries on same table

What I want to do is run a subquery from the same table in a single query my SQL is below:

    SELECT 
        DATEPART(DAY, dateTimeCreated) AS DateClicked,
        COUNT(*) AS NumClicks
    FROM [dbo].[tbl_clicks]
    WHERE 0 = 0
        AND DATEPART(MONTH, dateTimeCreated) = 'GIVEN MONTH'
        AND DATEPART(YEAR, dateTimeCreated) = 'GIVEN YEAR'
    GROUP BY DATEPART(DAY, dateTimeCreated)
    ORDER BY DATEPART(DAY, dateTimeCreated);

Straight forward and this gets me EXACTLY what I want – number of clicks on each day of a given month in a given year.

 DateClicked | NumClicks
---+-------------+------------
1            | 102215
2            | 256415
3            | 241314
etc ...

From THIS result, I’m looking to find how many of these clicks fell into a particular category, i.e. how many are to social media, how many are to email, etc. for EACH day (daily total, then breakdown of that total). I can get that fairly easily by adding another AND operator to the SQL,

AND clickType IN ( 'IDENTIFIER' )

but that involves creating another full statement; I want to have these results from one SINGLE query.

 DateClicked | NumClicks  |  Social |  Email | ...
---+-------------+--------------------------------
1            | 102215     |  38281  |  345   | ...
2            | 256415     |  12333  |  23    | ...
3            | 241314     |  44552  |  653   | ...
etc ...

Looping the results in various programming languages gives me the desired result, but it’s not the most efficient means. I’ve tried unions and various joins, but I’m not finding the right spot to run the subquery.

Answer

Just use conditional aggregation:

SELECT DAY(dateTimeCreated) AS DateClicked,
       COUNT(*) AS NumClicks,
       SUM(CASE WHEN clickType = 'Social' THEN 1 ELSE 0 END) as social,
       SUM(CASE WHEN clickType = 'Email' THEN 1 ELSE 0 END) as email,
       . . . 
FROM [dbo].[tbl_clicks]
WHERE MONTH(dateTimeCreated) = 'GIVEN MONTH'
      YEAR(dateTimeCreated) = 'GIVEN YEAR'
GROUP BY DAY(dateTimeCreated)
ORDER BY DAY(dateTimeCreated);

Advertisement

Answer