CODEWITHSUNDEEP
sqlsql-server
Shakti
Shakti

Reputation: 2033

How to show results of SQL aggregate functions in a column?

I have to calculate various SQL analytics and finally store them as key value.

For example 

Select col1, col2, sum(col3),
count(col4)
From Table
Group by col1,col2

Result

Col1 | col2 | sum(col3) | count(col4)
   X |   Y  |     5     |     10

But I want the result in the format

Key                          | Value
-----------------------------+------
Concat ( col1,col2, "sum")   |   5
Concat (col1,col2, " count") |  10

Can someone please help me with it.

Thanks

Upvotes: 2

Views: 113

Answers (2)

SouravA
SouravA

Reputation: 5243

A very simplified version of what you want would be the below using UNION operator.

select CAST('Concat ( col1,col2, "sum")' AS VARCHAR(50)) Key, value
from
(
Select col1, col2, sum(col3) [value]
From Table
Group by col1,col2
)A

UNION ALL

select CAST('Concat (col1,col2, " count")' AS VARCHAR(50)), value
from
(
Select col1, col2, count(col4) [value]
From Table
Group by col1,col2
)B

EDIT You can also compute it once and use it later for better performance.

Select col1, col2, sum(col3) sumval, count(col4) countval
into #Table
From Table
Group by col1,col2

select CAST('Concat ( col1,col2, "sum")' AS VARCHAR(50)) Key, sumval
from #Table    
UNION ALL 
select CAST('Concat (col1,col2, " count")' AS VARCHAR(50)), countval
from #Table

Upvotes: 2

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

You need to perform UNPIVOT. Here is one of the many articles on this topic.

As you correctly said, UNION ALL would need to run the query several times.

Here is one possible way to do it while calculating aggregated values only once. See also SQL Fiddle

Sample data

DECLARE @T TABLE(col1 varchar(50), col2 varchar(50), col3 int, col4 int);

INSERT INTO @T(col1, col2, col3, col4) VALUES 
('X', 'Y', 1, 10),
('X', 'Y', 2, 20),
('X', 'Y', 3, 30),
('X', 'Y', 4, 40),
('X', 'Y', 5, 50),
('A', 'B', 1, 10),
('A', 'B', 2, 20),
('A', 'B', 3, 30);

SELECT
    col1
    ,col2
    ,SUM(col3) AS SumCol3
    ,COUNT(col4) AS CountCol4
FROM @T
GROUP BY col1, col2;

Result of simple grouping

col1    col2    SumCol3    CountCol4
A       B       6          3
X       Y       15         5

Group and then UNPIVOT results by joining to the table with two rows (sum, count).

WITH
CTE
AS
(
    SELECT
        col1
        ,col2
        ,SUM(col3) AS SumCol3
        ,COUNT(col4) AS CountCol4
    FROM @T
    GROUP BY col1, col2
)
,FunctionNames
AS
(
    SELECT ID, FunctionName
    FROM (VALUES (1, 'sum'),(2, 'count') )AS t(ID, FunctionName)
)
SELECT
    CTE.col1 + '_' + CTE.col2 + '_' + FunctionNames.FunctionName AS KeyName
    ,CASE
    WHEN FunctionNames.ID = 1 THEN CTE.SumCol3
    WHEN FunctionNames.ID = 2 THEN CTE.CountCol4
    END AS Value
FROM
    CTE
    CROSS JOIN FunctionNames
ORDER BY
    CTE.col1
    ,CTE.col2
    ,FunctionNames.ID;

final result set

KeyName      Value
A_B_sum      6
A_B_count    3
X_Y_sum      15
X_Y_count    5

unpivot

You can see in the plan that the main table @T in scanned and sorted only once and that aggregate is calculated only once.

Using UNION ALL

The query that uses UNION ALL produces same results (with slightly different sorting), but you can see in the execution plan, that the main table was scanned and sorted twice.

SELECT
    col1 + '_' + col2 + '_sum' AS KeyName
    ,SUM(col3) AS Value
FROM @T
GROUP BY col1, col2

UNION ALL

SELECT
    col1 + '_' + col2 + '_count' AS KeyName
    ,COUNT(col4) AS Value
FROM @T
GROUP BY col1, col2

ORDER BY KeyName;

union all

Upvotes: 2

Related Questions