Group by giving me a different total count

Question

I am doing an sql query where I retrieve a list over Accounts for a specific year. I thought the below code was fine.

    SELECT count(DISTINCT ACCOUNT) AS ACCOUNT,
       CASE when MONTH(Created)=1 then 'January' 
        when MONTH(Created)=2 then 'February'
        when MONTH(Created)=3 then 'March'
        when MONTH(Created)=4 then 'April'
        when MONTH(Created)=5 then 'May'
        when MONTH(Created)=6 then 'June'
        when MONTH(Created)=7 then 'July'
        when MONTH(Created)=8 then 'August'
        when MONTH(Created)=9 then 'September'
        when MONTH(Created)=10 then 'October'
        when MONTH(Created)=11 then 'November'
        when MONTH(Created)=12 then 'December'
        else '3' end AS MONTH
   FROM DB_Table
   WHERE
    AND (DB_Table.Cancelled < '1901-01-01' OR DB_Table Cancelled > '2017-12-31')    
    AND YEAR(Created)='2017'
   GROUP BY MONTH(Created)
   Order BY MONTH(Created)

Until I did a check this way:

    Select count(DISTINCT ACCOUNT) AS ACCOUNT
    FROM DB_Table
    WHERE
    AND (DB_Table.Cancelled < '1901-01-01' OR DB_Table Cancelled > '2017-12-31')    
    AND YEAR(Created)='2017'

I'm having two different sums, but it seems like the Group by is giving me a higher count than the bottom query. Any suggestions?

Well ya, you surely have more distinct values for each account/month combo than accounts alone. Unless you only have one month of data. Know what I mean? — Jacob H, Mar 15 '18 at 13:18
Yes understandable, is it possible to do with the above query? Use something like having or so? — pancake, Mar 15 '18 at 13:24
What are you trying to achieve here? You ask "is it possible to do with the above query", is *what* possible? — HoneyBadger, Mar 15 '18 at 13:31
My apologies - Is it possible to return the same count with the top query like I do with the bottom query? The "Distinct" should be applied for the whole year and not every month — pancake, Mar 15 '18 at 13:34
The easiest way would be with a subquery, but what's the point of the month case expression then? — HoneyBadger, Mar 15 '18 at 13:43
Do you mean something like a query for each month with a union all? The above query is just to have less code... I guess? — pancake, Mar 15 '18 at 13:46
And the month case expression is to get the names of the months — pancake, Mar 15 '18 at 13:59
Can you add some sample data and expected output? It's unclear what you are looking for. — HoneyBadger, Mar 15 '18 at 15:36

score 0 · Answer 1 · answered Mar 15 '18 at 14:02

The query looks fine, the reason is probably you have same data in different months, for example, one account in January and same three in September. When you query the global it only counts one. By the way, I strongly recommend to use MONTHNAME (mysql) or other function to faster the query.

Convert Month Number to Month Name Function in SQL

score 0 · Answer 2 · answered Mar 15 '18 at 14:06

Just taking a stab in the dark here, since you're not clear on your input/desired output, but my guess is that perhaps you're just trying to do this (DATENAME is SQL Server, FYI):

SELECT COUNT(ACCOUNT) AS ACCOUNTs,
   DATENAME(month, Created) AS FullMonthName
FROM DB_Table
WHERE (DB_Table.Cancelled < '1901-01-01' OR DB_Table Cancelled > '2017-12-31')    
AND YEAR(Created)='2017'
GROUP BY DATENAME(month, Created)

Group by giving me a different total count

2 Answers2