CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
AS91
AS91

Reputation: 527

Count Top 5 Elements spread over rows and columns

Using T-SQL for this table:

+-----+------+------+------+-----+
| No. | Col1 | Col2 | Col3 | Age |
+-----+------+------+------+-----+
|   1 | e    | a    | o    |   5 |
|   2 | f    | b    | a    |  34 |
|   3 | a    | NULL | b    |  22 |
|   4 | b    | c    | a    |  55 |
|   5 | b    | a    | b    |  19 |
+-----+------+------+------+-----+

I need to count the TOP 3 names (Ordered by TotalCount DESC) across all rows and columns, for 3 Age groups: 0-17, 18-49, 50-100. Also, how do I ignore the NULLS from my results?

If it's possible, how I can also UNION the results for all 3 age groups into one output table to get 9 results (TOP 3 x 3 Age groups)?

Output for only 1 Age Group: 18-49 would look like this:

+------+------------+
| Name | TotalCount |
+------+------------+
| b    |          4 |
| a    |          3 |
| f    |          1 |
+------+------------+

Upvotes: 2

Views: 135

Answers (2)

Eralper
Eralper

Reputation: 6622

You can check below multiple-CTE SQL select statement Row_Number() with Partition By clause is used ordering records within each group categorized by ages

    /*
CREATE TABLE tblAges(
    [No]      Int,
    Col1    VarChar(10),
    Col2    VarChar(10),
    Col3    VarChar(10),
    Age     SmallInt
)
INSERT INTO tblAges VALUES
(1, 'e',    'a',        'o',    5),
(2, 'f',    'b',        'a',    34),
(3, 'a',    NULL,       'b',    22),
(4, 'b',    'c',        'a',    55),
(5, 'b',    'a',        'b',    19);
*/
;with cte as (
    select
        col1 as col, Age
    from tblAges
    union all
    select
        col2, Age
    from tblAges
    union all
    select
        col3, Age
    from tblAges
), cte2 as (
    select 
        col,
        case 
            when age < 18 then '0-17'
            when age < 50 then '18-49'
            else '50-100'
        end as grup
    from cte 
    where col is not null
), cte3 as (
select
    grup,
    col,
    count(grup) cnt
from cte2
group by
    grup,
    col
)
select * from (
select
grup, col, cnt, ROW_NUMBER() over (partition by grup order by cnt desc)  cnt_grp
from cte3
) t
where cnt_grp <= 3
order by grup, cnt

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You need to unpivot first your table and then exclude the NULLs. Then do a simple COUNT(*):

WITH CteUnpivot(Name, Age) AS(
    SELECT x.*
    FROM tbl t
    CROSS APPLY ( VALUES
        (col1, Age),
        (col2, Age),
        (col3, Age)
    ) x(Name, Age)
    WHERE x.Name IS NOT NULL
)
SELECT TOP 3
    Name, COUNT(*) AS TotalCount
FROM CteUnpivot
WHERE Age BETWEEN 18 AND 49
GROUP BY Name
ORDER BY COUNT(*) DESC

ONLINE DEMO

If you want to get the TOP 3 for each age group:

WITH CteUnpivot(Name, Age) AS(
    SELECT x.*
    FROM tbl t
    CROSS APPLY ( VALUES
        (col1, Age),
        (col2, Age),
        (col3, Age)
    ) x(Name, Age)
    WHERE x.Name IS NOT NULL
),
CteRn AS (
    SELECT
        AgeGroup =
            CASE 
                WHEN Age BETWEEN 0 AND 17 THEN '0-17'
                WHEN Age BETWEEN 18 AND 49 THEN '18-49'
                WHEN Age BETWEEN 50 AND 100 THEN '50-100'
            END,
        Name,
        COUNT(*) AS TotalCount
    FROM CteUnpivot
    GROUP BY 
        CASE 
            WHEN Age BETWEEN 0 AND 17 THEN '0-17'
            WHEN Age BETWEEN 18 AND 49 THEN '18-49'
            WHEN Age BETWEEN 50 AND 100 THEN '50-100'
        END,
        Name
)
SELECT
    AgeGroup, Name, TotalCount
FROM(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY AgeGroup, Name ORDER BY TotalCount DESC)
    FROM CteRn
) t
WHERE rn <= 3;

ONLINE DEMO

The unpivot technique using CROSS APPLY and VALUES:

An Alternative (Better?) Method to UNPIVOT (SQL Spackle) by Dwain Camps

Upvotes: 3

Related Questions