Include null/0 rows in a group by

Question

I want to query a database for users and the the amount of time they spent on each Activity Category. These categories are stored in the table ActivityCategory (int Id, varchar Name). There's only 8 of them and I want to see all of them, even when nobody spent time on a specific category.

I have the following query:

select u.NoEmploye, u.FirstName, u.LastName, Total=sum(h.NbHeures), ac.Name from Users u join Semaines s on u.EntityGuid=s.UserGuid join HeuresProjets hp on s.Id=hp.WeekId join Heures h on hp.HPId=h.HpGuid join ActivityCodes code on h.Code=code.ActivityId join ActivityCategory ac on code.Categorie=ac.Id group by u.NoEmploye, u.FirstName, u.LastName, ac.Name order by u.NoEmploye

It works fine but it doesn't return unused ActivityCategories. I tried every combination of full/left/right/inner/outer/you-name-it joins. The best I could get is completely null rows when a category is used by nobody and a null ac.Name for categories a specific user doesn't use but others do. I suspect the group by [...]ac.Name part is what's "eating" the unused categories. What am I doing wrong? Should I write a select query the a second one to group the results? I'll have a dozen more similar queries to write so I'd like to understand instead of just having a fixed query with no explanation.

EDIT Lamak's second query works so far but it has the same problem when I add a where clause.

EDIT2 ypercube' edit works perfectly

Now let's see if I understand the query correctly. I coalesce the Sum with 0 to have a proprer value when the result would be null. I start the selection from ActivityCategory to make sure I have all of them, even the unused ones, which I cross join with users to have every combination of ActivityCategory and Users. I left join Activitycodes to only get relevant rows and then I inner join my other tables to get to Semaine. My condition are applied to the Semaine table's join because I want to filter my data before the cross join. Finally, I group my data by Users and ActivityCategory to have the sum works.

ypercubeᵀᴹ · Accepted Answer · 2013-03-15 22:50:06Z

This should do:

SELECT u.NoEmploye, u.FirstName, u.LastName, Total=sum(h.NbHeures), ac.Name FROM ActivityCategory ac LEFT JOIN ActivityCodes code ON code.Categorie=ac.Id LEFT JOIN Heures h ON h.Code=code.ActivityId LEFT JOIN HeuresProjets hp ON hp.HPId=h.HpGuid LEFT JOIN Semaines s ON s.Id=hp.WeekId LEFT JOIN Users u ON u.EntityGuid=s.UserGuid GROUP BY u.NoEmploye, u.FirstName, u.LastName, ac.Name ORDER BY u.NoEmploye

Basically, if you want all Categories, you need to use that table as the first table on your FROM and do LEFT JOINs to that table.

UPDATE If you want every category for every user on your results, you'll need a CROSS JOIN:

SELECT u.NoEmploye, u.FirstName, u.LastName, Total=sum(h.NbHeures), ac.Name FROM ActivityCategory ac CROSS JOIN Users u LEFT JOIN ActivityCodes code ON code.Categorie=ac.Id LEFT JOIN Heures h ON h.Code=code.ActivityId LEFT JOIN HeuresProjets hp ON hp.HPId=h.HpGuid LEFT JOIN Semaines s ON s.Id=hp.WeekId AND u.EntityGuid=s.UserGuid GROUP BY u.NoEmploye, u.FirstName, u.LastName, ac.Name ORDER BY u.NoEmploye

To solve the issue when you want to add a WHERE clause:

SELECT u.NoEmploye, u.FirstName, u.LastName, Total=COALESCE(SUM(h.NbHeures),0), ac.Name FROM ActivityCategory ac CROSS JOIN Users u LEFT JOIN ActivityCodes code JOIN Heures h ON h.Code=code.ActivityId JOIN HeuresProjets hp ON hp.HPId=h.HpGuid JOIN Semaines s ON s.Id=hp.WeekId AND (some condition on the dates) -- add here ON ac.Id = code.Categorie AND u.EntityGuid = s.UserGuid GROUP BY u.NoEmploye, u.FirstName, u.LastName, ac.Name ORDER BY u.NoEmploye

Your logic seems right but when I run your query, sql server returns 8 rows (1 for each category) with null user data
8 rows in addition to the results I mean. And users still don't have a row for unused categories
@fhlamarche Ok. I think that you'll need to post some sample data and expected results for me (and other users) to provide a better answer.
You need to do a cross join to achieve what you want. However, your database structure is too complex to replicate (I'm lazy). I suggest you create a SQLFiddle item and send over the link. I can code your sql from there.
@fhlamarche In the results, do you want a row for every combination of User and ActivityCategory?

Collectives™ on Stack Overflow

Include null/0 rows in a group by

1 Answer 1

10 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Related