I have a query to return how much is spent on-contract and off-contract at each location, that returns something like this:
Location | ContractStatus | Expenses
-------------+----------------+---------
New York | Ad-hoc | 2043.47
New York | Contracted | 2894.57
Philadelphia | Ad-hoc | 3922.53
Seattle | Contracted | 2522.00
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:
Location | ContractStatus | Expenses
-------------+----------------+---------
New York | Ad-hoc | 2043.47
New York | Contracted | 2894.57
Philadelphia | Ad-hoc | 3922.53
Philadelphia | Contracted | 0.00
Seattle | Ad-hoc | 0.00
Seattle | Contracted | 2522.00
Is there any way I can accomplish this through SQL? Here is the actual query I'm using (SQL Server 2005):
SELECT Location,
CASE WHEN Orders.Contract_ID IS NULL
THEN 'Ad-hoc' ELSE 'Contracted' END
AS ContractStatus,
SUM(OrderTotal) AS Expenses
FROM Orders
GROUP BY Location,
CASE WHEN Orders.Contract_ID IS NULL
THEN 'Ad-hoc' ELSE 'Contracted' END
ORDER BY Location ASC, ContractStatus ASC
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...
SELECT Location,
Sum(Case When Contract_ID Is Null Then OrderTotal Else 0 End) AdHoc,
Sum(Case When Contract_ID Is Null Then 0 Else OrderTotal End) Contracted
FROM Orders
GROUP BY Location
if you reallly want separate rows for each, then one approach would be to:
SELECT Location, Min('AdHoc') ContractStatus,
Sum(Case When Contract_ID Is Null
Then OrderTotal Else 0 End) OrderTotal
FROM Orders
GROUP BY Location
Union
SELECT Location, Min('Contracted') ContractStatus,
Sum(Case When Contract_ID Is Null
Then 0 Else OrderTotal End) OrderTotal
FROM Orders
GROUP BY Location
Order By Location
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With