CODEWITHSUNDEEP
sqlsql-server
Peter
Peter

Reputation: 9

Group by year month sql, when no entry write 0

I have an sql query, but I want to show months, where no entries. Now just show entries when it has entry. Here is the code:

    SELECT YEAR(T0.[Recontact]) AS 'Év', MONTH(T0.[Recontact]) AS 'Hónap',
           T1.[SlpName], COUNT(T0.[ClgCode]) AS 'Tárgyalások'
    FROM OCLG T0
    INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]
    WHERE T0.[Action] = 'M' AND
    T0.[Recontact] >= 'date' AND
    T0.[Recontact] <= 'date2' AND
    T1.[SlpName] = 'user name'
   GROUP BY YEAR(T0.[Recontact]), MONTH(T0.[Recontact]), T1.[SlpName]
   ORDER BY 1,2

Upvotes: 0

Views: 103

Answers (3)

James Z
James Z

Reputation: 12317

If the year + month is totally missing from your data, you'll need to construct an empty row somewhere that can be shown in the place. You can create either a calendar table (one row per day) or a month table (one row per month). That can also be a "virtual" tally table constructed in a CTE or similar.

Once you have that, you can do something like this:

select 
  M.Year, M.Month, X.SlpName, isnull(X.CODES,0) as CODES
from 
  months M
  outer apply (
    SELECT 
      YEAR(T0.[Recontact]) as Year, 
      MONTH(T0.[Recontact]) AS Month,
      T1.[SlpName], 
      COUNT(T0.[ClgCode]) AS CODES
    FROM OCLG T0
    INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]
    WHERE T0.[Action] = 'M' AND
    T0.[Recontact] >= 'date' AND
    T0.[Recontact] <= 'date2' AND
    T1.[SlpName] = 'user name'
   GROUP BY YEAR(T0.[Recontact]), MONTH(T0.[Recontact]), T1.[SlpName]
) X on X.Year = M.Year and X.Month = M.Month
where M.MONTHDATE >= 'date' and M.MONTHDATE <= 'date2'
ORDER BY 1,2

This was with an imaginary month table that has year, month and monthdate columns, and the date is the first of the month -- you'll still have to check that the range you're fetching is correct.

I haven't tested this, but it should work.

Upvotes: 1

vaheed sayyad
vaheed sayyad

Reputation: 1

Replace INNER JOIN with LEFT JOIN in your request to get NULL results. to learn more visit : http://academy.comingweek.com/sql-groupby-clause/

Upvotes: 0

user6307642
user6307642

Reputation:

Replace INNER JOIN with LEFT JOIN in your request to get NULL results.

SELECT YEAR(T0.[Recontact]) AS 'Év', MONTH(T0.[Recontact]) AS 'Hónap',
       T1.[SlpName], COUNT(T0.[ClgCode]) AS 'Tárgyalások'
FROM OCLG T0
LEFT JOIN OSLP T1 ON (T0.[SlpCode] = T1.[SlpCode]
                      AND T1.[SlpName] = 'user name')
WHERE T0.[Action] = 'M'
  AND T0.[Recontact] >= 'date'
  AND T0.[Recontact] <= 'date2'
GROUP BY YEAR(T0.[Recontact]), MONTH(T0.[Recontact]), T1.[SlpName]
ORDER BY 1,2

Upvotes: 0

Related Questions