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.
month(InvoiceDate) as 'month',
year(InvoiceDate) as 'year',
case when CATEGORIES.ParentCategoryID in (125,183) and CATEGORIES.CategoryID not in (162, 163, 164, 165) then
end as Qnt
-- ,CATEGORIES.CategoryID -- this line showed me that it does not group by categoryid somehow...
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,
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
ParentCategoryId out of the
group by clause.