CODEWITHSUNDEEP
sqlsql-serverinner-join
Maciek Stodulski
Maciek Stodulski

Reputation: 5

SQL how can i return 0 in join clause

select datepart(year, data) Year, DATEPART(month, data) Month,
       Coalesce(sum(number),0) as sum
from drugsEdition inner join
     visit
     on drugsEdition.idVisit = visit.id inner join
     drugsSeries
     on drugsEdition.idDrugSeries = drugsSeries.id inner join
     drugs
     on drugsSeries.idDrugs = drugs.id
where idDrugs = 153 or idDrugs = 1241
group by DATEPART(year, data), DATEPART(month, data)
order by Year, Month

Hello,

I have a problem, how i can return 0 in this case. I want to return the sum number of drugs used in each month, but this code does not return the months with result 0. example:

Year    Month   sum
2014    3   3,9
2014    4   2,8
2014    5   0,7
2014    8   2,6
2014    9   0,5
2014    10  2,4

month 7 isn't here

Upvotes: 0

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

There are two basic approach to this. One is to use a list of valid months and then left join to include all of them. The is the more general purpose method -- although somehow you need to generate the list of months.

In your case, though, it is quite possible that there are records in the table for each month, but they are filtered out in the where clause. If so, you can use conditional aggregation:

select datepart(year, data) as yyyy, DATEPART(month, data) as mon,
       sum(case when idDrugs in (153, 1241) then number else 0 end) as Total
from drugsEdition inner join
     visit
     on drugsEdition.idVisit = visit.id inner join
     drugsSeries
     on drugsEdition.idDrugSeries = drugsSeries.id inner join
     drugs
     on drugsSeries.idDrugs = drugs.id
group by DATEPART(year, data), DATEPART(month, data)
order by yyyy, mon;

Note that column names such as year, month, and sum are bad choices. These are SQL keywords or function names. It is better to steer clear of the handful of names reserved by the language.

Upvotes: 1

Related Questions