JP Hellemons JP Hellemons - 6 months ago 8
SQL Question

SQL grouping issue cause duplicate rows instead of grouped

Somehow my SQL does not group properly. I want to have just one line per invoice. I do not want the column categoryid but I think that caused the query to output two lines (or more) per invoice.

select invoices.InvoiceID,
InvoiceDate,
month(InvoiceDate) as 'month',
year(InvoiceDate) as 'year',
CustomerCompanyName,
countryname,
case when CATEGORIES.ParentCategoryID in (125,183) and CATEGORIES.CategoryID not in (162, 163, 164, 165) then
count(SHIPMENTPRODUCTS.ProductID)
else
0
end as Qnt
-- ,CATEGORIES.CategoryID -- this line showed me that it does not group by categoryid somehow...
from INVOICES
inner join CUSTOMERS on invoices.CustomerID = customers.CustomerID
inner join COUNTRIES on CUSTOMERS.CountryID = COUNTRIES.CountryID
inner join shipments on shipments.invoiceid = invoices.invoiceid and shipments.ShipmentCancelled = 0
inner join SHIPMENTPRODUCTS on shipments.shipmentid = SHIPMENTPRODUCTS.ShipmentID
inner join products on SHIPMENTPRODUCTS.ProductID = products.productid
inner join CATEGORIES on products.CategoryID = CATEGORIES.CategoryID
group by invoices.InvoiceID,
invoices.InvoiceDate,
customers.customercompanyname,
countries.CountryName,
CATEGORIES.ParentCategoryID,
CATEGORIES.CategoryID


enter image description here

I know I can fix this by inserting the results in a temptable and grouping that. or by using a sub-select. But I'd like to just have this fixed without a subselect or temp table. It must be a small error in my grouping or joins but I do not see it.

Answer

You probably want conditional aggregation:

select invoices.InvoiceID, InvoiceDate,
       month(InvoiceDate) as [month], year(InvoiceDate) as [year],
       CustomerCompanyName, countryname,
       sum(case when CATEGORIES.ParentCategoryID in (125,183) and CATEGORIES.CategoryID not in (162, 163, 164, 165)
                then 1 else 0
           end) as Qnt

Then leave CategoryId and ParentCategoryId out of the group by clause.