Skip to content
Advertisement

How to return empty groups in SQL GROUP BY clause

I have a query to return how much is spent on-contract and off-contract at each location, that returns something like this:

The problem is, I only get one row for locations that are all ad-hoc or all contracted expenses. I’d like to get two rows back for each location, like this:

Is there any way I can accomplish this through SQL? Here is the actual query I’m using (SQL Server 2005):

Advertisement

Answer

Yes, construct an expression that returns the ordertotal for adhoc only, and 0 for the others, and another one that does the opposite, and sum those expressions. This will include one row per location with two columns one for adhoc and one for Contracted…

if you reallly want separate rows for each, then one approach would be to:

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement