SQL Server Group By and Count

Question

I am trying to do a count of invoices by country within region. Below is a view of my table structure and required output. I have tried to do a basic view (not looking at CustomerArea yet as I want to get the basics right first) of this but cannot get into a nested mode.

SELECT Customerregion, COUNT(InvoiceNo ) AS CountCol1
FROM [Data-Warehouse].[dbo].[Master]
WHERE Customerregion='Europe'
GROUP BY Customerregion, InvoiceNo

Below is a view of the output for Europe only based on the above?

CustomerArea    CustomerRegion  InvoiceNo
Romania Europe  INV001
Romania Europe  INV002
Netherlands Europe  INV003
Netherlands Europe  INV003
Netherlands Europe  INV003
Netherlands Europe  INV004
Italy   Europe  INV005
Italy   Europe  INV005

And why are you grouping by customerregion if you restrict you result to one single customerregion — derpirscher
– derpirscher, Commented Jul 26, 2022 at 9:00
Thank you derpirscher, you are right - that can be removed. There will be multiples area and I added the clause to try and get to a result. — Mr Jackson
– Mr Jackson, Commented Jul 26, 2022 at 9:02
Please don't show your sample data as image, but as text. If it was text then we could copy/paste it to create a temporary table and try out your query, correct it and then provide you with an answer. But since it is in an image, we would have to type over all your sample data, that is not our job but yours. Thank you — GuidoG
– GuidoG, Commented Jul 26, 2022 at 9:22

Dale K · Accepted Answer · 2024-05-11 08:12:55Z

1

I would imagine all you need is this. Once you have this data you can count the totals for a region and also do a grand total in the calling code.

DROP TABLE IF EXISTS #DataTable;

CREATE TABLE #DataTable (
    CustomerArea VARCHAR(20),
    CustomerRegion VARCHAR(20),
    InvoiceNo VARCHAR(6)
);

INSERT INTO #DataTable (CustomerArea, CustomerRegion, InvoiceNo)
VALUES
('Romania', 'Europe', 'INV001'),
('Romania', 'Europe', 'INV002'),
('Netherlands', 'Europe', 'INV003'),
('Netherlands', 'Europe', 'INV003'),
('Netherlands', 'Europe', 'INV003'),
('Netherlands', 'Europe', 'INV004'),
('Italy', 'Europe', 'INV005'),
('Italy', 'Europe', 'INV005');

SELECT CustomerRegion, CustomerArea, COUNT(*) AS NumberOfInvoices
FROM #DataTable
GROUP BY CustomerArea, CustomerRegion

edited May 11, 2024 at 8:12

Dale K

28.4k15 gold badges62 silver badges88 bronze badges

answered Jul 26, 2022 at 9:35

Andrew

2,9863 gold badges38 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mr Jackson Over a year ago

Thank you Andrew - I get totally where it is going wrong now - thank you again. So, what I was trying to get to ultimately is what you produced, but clearly I got myself confused very quickly earlier in the play. Overthinking it probably, I don't know. Cheers

Collectives™ on Stack Overflow

SQL Server Group By and Count

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related