Sql-server – Return even empty groups when grouping by date

group bysql servert-sql

I have the following query:

SELECT DISTINCT
           COUNT(CD_BarCode)
           ,CD_Carrier
           ,SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11) Date
FROM TB_AGIL 
WHERE 
           DT_Arriving >= @date
           AND DT_Arriving < DATEADD(MONTH,+1,@date)
           AND CD_TRACKING = 14
GROUP BY CD_Carrier, SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11)
ORDER BY Date

I use this to see the results from last month's cargo movimentation. The results are a list of cd_tracking amount grouped by date like this:

| Amount | Carrier |    Date    |
|--------|---------|------------|
|  2599  |   44    | 01/08/2015 |
|  2504  |   44    | 03/08/2015 |
|  4597  |   44    | 04/08/2015 |
|  5058  |   44    | 05/08/2015 |
|  2413  |   44    | 06/08/2015 |
|  4853  |   44    | 07/08/2015 |

My problem is that if there is no cd_tracking = 14 on a given day, that day will not show on the result list. As you can see, there is no Aug 2nd on the list.

I'd like to know if there is a way to bring every day on the result list, even if there is no cd_tracking = 14 on that day. Like this:

| Amount | Carrier |    Date    |
|--------|---------|------------|
|  2599  |   44    | 01/08/2015 |
|  NULL  |   44    | 02/08/2015 |
|  2504  |   44    | 03/08/2015 |
|  4597  |   44    | 04/08/2015 |
|  5058  |   44    | 05/08/2015 |
|  2413  |   44    | 06/08/2015 |
|  4853  |   44    | 07/08/2015 |

Best Answer

Move the condition from the WHERE clause to a conditional count:

SELECT
    COUNT(CASE WHEN CD_TRACKING = 14 THEN CD_BarCode ELSE NULL END),
    CD_Carrier,
    DATEPART(DAY, DT_Arriving) as [Date]
FROM TB_AGIL 
WHERE
    DT_Arriving >= @date
    AND DT_Arriving < DATEADD(MONTH,+1,@date)
GROUP BY 
    CD_Carrier, 
    DATEPART(DAY, DT_Arriving)
ORDER BY 
    [Date];

Other approaches to implement the deprecated GROUP BY ALL syntax can be found here.

Related Question