I have a table that contains the following columns: TrackingStatus, Year, Month, Order, Notes

I need to calculate the total number of tracking status for each year and month.

For example, if the table contains the following orders:

The result should be:

• Tracking F , year 2020, month 1 the total will be one (because it's the same year, month, and order).
• Tracking A , year 2021, month 2 the total will be one. (because there is only one record with the same year, month, and order).
• Tracking A , year 2021, month 3 the total will be two. (because there are two orders within the same year and month).

So the expected SELECT output will be like that:

I was trying to use group by but then it will count the number of records which in my scenario is wrong.

How can I get the total orders for each month without counting “duplicate” records?

Thank you

CodePudding user response：

You can use a COUNT DISTINCT aggregation function, whereas the COUNT allows you to count the values, but the DISTINCT condition will allow each value only once.

SELECT TrackingStatus,
       Year,
       Month,
       COUNT(DISTINCT Order) AS Total
FROM tab
GROUP BY TrackingStatus,
         Year,
         Month
ORDER BY Year, 
         Month

Here you can find a tested solution in a MySQL environment, though this should work with many DBMS.