SQL Server Group By and Count-CodePudding

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?

I am sorry to ask a simple question here.

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

CodePudding user response：

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