CODEWITHSUNDEEP
sqlsql-servergroup-bysumunion
Thomas Short
Thomas Short

Reputation: 49

Generate a summary using 2 different columns

I am trying to generate a summary of total calls by a sale group for a specific product. However the product is listed in either P1 or P2 as up to two products can be mentioned on a single call. The code I have is 

SELECT  SALESFORCE_ID,P1 AS PRODUCT,SUM(Calls) as Calls 
            FROM Calls
                GROUP BY SALESFORCE_ID, P1
UNION
SELECT SALESFORCE_ID,P2,SUM(Calls) as Calls
            FROM Calls
                WHERE P2 <> ''
                GROUP BY SALESFORCE_ID, P2

and it generates the following results 

SALESFORCE_ID                                      PRODUCT                                            Calls
-------------------------------------------------- -------------------------------------------------- -----------
SALESFORCE_HP                                      PRODUCT_E                                          18111
SALESFORCE_HP                                      PRODUCT_E                                          83925
SALESFORCE_HP                                      PRODUCT_S                                          17931
SALESFORCE_HP                                      PRODUCT_S                                          87390
SALESFORCE_SP                                      PRODUCT_M                                          382230
SALESFORCE_SP                                      PRODUCT_W                                          159534
SALESFORCE_SSTR                                    PRODUCT_E                                          2751
SALESFORCE_SSTR                                    PRODUCT_M                                          3276
SALESFORCE_SSTR                                    PRODUCT_S                                          4314
SALESFORCE_SSTR                                    PRODUCT_W                                          948
SALESFORCE_STR                                     PRODUCT_M                                          10347
SALESFORCE_STR                                     PRODUCT_M                                          45963
SALESFORCE_STR                                     PRODUCT_S                                          22263
SALESFORCE_STR                                     PRODUCT_S                                          23787

How do I then sum the results where Sales group and product are equal?

Upvotes: 0

Views: 21

Answers (2)

Dmitriy Bayraktar
Dmitriy Bayraktar

Reputation: 126

You probably need this, as more elegant and performant way

WITH TotalCalls AS
(SELECT SALESFORCE_ID,
       P1 AS PRODUCT,
       Calls
FROM Calls
UNION ALL
SELECT SALESFORCE_ID,
       P2 AS PRODUCT,
       Calls
FROM Calls
WHERE P2 <> '')

SELECT SALESFORCE_ID, PRODUCT, SUM(Calls) AS Calls
FROM TotalCalls
GROUP BY SALESFORCE_ID, PRODUCT

Upvotes: 0

GMB
GMB

Reputation: 222542

How do I then sum the results where Sales group and product are equal?

You can add another level of aggregation:

select salesforce_id, product, sum(calls) calls
from (
    select salesforce_id,p1 as product,sum(calls) as calls 
    from calls
    group by salesforce_id, p1
    union all
    select salesforce_id,p2,sum(calls) as calls
    from calls
    where p2 <> ''
    group by salesforce_id, p2
) t
group by salesforce_id, product

As far as I see there is no point using union (which is meant to de-duplicate the results): union all is good enough, and incurs less work for the database.

Upvotes: 1

Related Questions