I have a table with Names, Countries and Status. I want get total by grouping by Names and Status but get only Top 3 Countries.
My table:
+------+---------+--------+
| Name | Country | Status |
+------+---------+--------+
| ABC | US | Open |
| ABC | US | Closed |
| ABC | US | Open |
| ABC | Japan | Open |
| ABC | Japan | Closed |
| ABC | China | Open |
| ABC | China | Closed |
| ABC | Italy | Open |
| DEF | US | Open |
| DEF | US | Closed |
| DEF | Japan | Open |
| DEF | Japan | Closed |
| DEF | China | Open |
| DEF | China | Closed |
| DEF | China | Closed |
| DEF | Italy | Open |
+------+---------+--------+
Desired output:
+------+---------+--------+-------+
| Name | Country | Status | Total |
+------+---------+--------+-------+
| ABC | US | Open | 2 |
| ABC | US | Closed | 1 |
| ABC | Japan | Open | 1 |
| ABC | Japan | Closed | 1 |
| ABC | China | Open | 1 |
| ABC | China | Closed | 1 |
| DEF | US | Open | 1 |
| DEF | US | Closed | 1 |
| DEF | Japan | Open | 1 |
| DEF | Japan | Closed | 1 |
| DEF | China | Open | 1 |
| DEF | China | Closed | 2 |
+------+---------+--------+-------+
I tried the following query but it didn't give me result I am looking for.
Select rs.Name, rs.Country, rs.Status, Count(*) as total from (
SELECT Name, Country, Status, Rank()
over (Partition BY Name
ORDER BY Country DESC ) AS Rank
FROM table1 ) rs WHERE Rank <= 3
You can use the following query:
;With CTE AS (
SELECT Name, Country, Status,
COUNT(*) OVER (PARTITION BY Name, Country) AS cnt
FROM mytable
), CTE2 AS (
SELECT Name, Country, Status,
DENSE_RANK() OVER (PARTITION BY Name ORDER BY cnt DESC, Country) AS seq
FROM CTE
)
SELECT Name, Country, Status, COUNT(*) AS Total
FROM CTE2
WHERE seq <= 3
GROUP BY Name, Country, Status
ORDER BY Name, Country
In case of the ties, the query picks the Country having the 'smallest' name in comparison to the other countries.