Abdulsalam Elsharif Abdulsalam Elsharif - 27 days ago 21
SQL Question

How can I used nested Group by in SQL

I have 3 tables : Invoice, ServicesinInvoice and Services (see attached picture)

enter image description here

I want to create a summary report that show the daily sales for each service (cash total, debit total), I try this :

SELECT Service.ServiceName, SUM(ServicesinInvoice.Total) AS Total, COUNT(*) AS Count, Service.ServiceID
FROM ServicesinInvoice INNER JOIN
Service ON ServicesinInvoice.ServiceId = Service.ServiceID INNER JOIN
Invoice ON ServicesinInvoice.InvoiceId = Invoice.InvoiceID
WHERE (dbo.Invoice.InvoiceDate >= CONVERT(DATETIME, '2016-06-11 00:00:00', 102))
GROUP BY dbo.Service.ServiceName, dbo.Service.ServiceID


The about sql code gives me the total for each service, What I want is to get the cash total and debit total for each service (Grouped by service).

In the table (Invoice) I have ClientId column, If ClientId=1 that's mean cash, else is debit.

Please help to accomplish that

Thanks in advance.

Answer

If I understood you correctly, that can be done with conditional aggregation :

SELECT Service.ServiceID,Service.ServiceName, 
       SUM(CASE WHEN Invoice.ClientId = 1 THEN ServicesinInvoice.Total ELSE 0 END) AS Total_cash,
       SUM(CASE WHEN Invoice.ClientId <> 1 THEN ServicesinInvoice.Total ELSE 0 END) AS Total_debit,
       COUNT(*) AS Count 
FROM ServicesinInvoice INNER JOIN
     Service ON ServicesinInvoice.ServiceId = Service.ServiceID INNER JOIN
     Invoice ON ServicesinInvoice.InvoiceId = Invoice.InvoiceID
WHERE (dbo.Invoice.InvoiceDate >= CONVERT(DATETIME, '2016-06-11 00:00:00', 102)) 
GROUP BY dbo.Service.ServiceName, dbo.Service.ServiceID
Comments