SQL Server – How to Sum Each Enum Value in Different Column Without Repeating Names

enumsql serversql-server-2008sum

Let's say I have a table with SellerId, DateTime, ProductType to register each time a a Seller sells a product. The ProductType is from ENUM type with { 0=Phone, 1=Recharge, 2=SimCard }. Then I have a table with SellerId and SellerName.

I want to write a stored procedure that shows this result:

  ID | NAME | Phones | Recharges | SimCards | Total
 ----|------|--------|-----------|----------|-------
  05 | Eddy |      6 |         7 |        0 |    13

I know how to join the two tables to get the ID and NAME in this result set, but I do not know how to SUM each ProductType into a different column. And I also wanted only one row for each ID/NAME.

Can anyone show me how to achieve this in this simple example?

Best Answer

This is pretty simple to code using standard SQL if that is what you prefer.

CREATE TABLE dbo.seller
(SellerID INT,
 SellerName NVARCHAR(100));

CREATE TABLE dbo.productsold
(SellerID INT,
 SaleDate DATE,
 ProductEnum TINYINT);

-- Sample sellers 
INSERT INTO dbo.seller VALUES (1, 'Frank');
INSERT INTO dbo.seller VALUES (2, 'Sally');
-- Sample transactions
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 0);
INSERT INTO dbo.productsold VALUES (2, GETDATE(), 2);
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 2);
INSERT INTO dbo.productsold VALUES (2, GETDATE(), 0);
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 1);
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 1);
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 0);
INSERT INTO dbo.productsold VALUES (2, GETDATE(), 2);
INSERT INTO dbo.productsold VALUES (2, GETDATE(), 0);

-- Query to pivot and sum
SELECT s.SellerID, s.SellerName,
  SUM (CASE WHEN p.ProductEnum = 0 THEN 1 ELSE 0 END) AS Phones,
  SUM (CASE WHEN p.ProductEnum = 1 THEN 1 ELSE 0 END) AS Recharges,
  SUM (CASE WHEN p.ProductEnum = 2 THEN 1 ELSE 0 END) AS SimCards,
  COUNT (p.ProductEnum) AS Total
FROM dbo.seller AS s
   JOIN dbo.productsold AS p ON s.SellerID = p.SellerID
GROUP BY s.SellerID, s.SellerName
ORDER BY s.SellerID;

-- Drop test tables
DROP TABLE dbo.seller;
DROP TABLE dbo.productsold;

RESULTS:

SellerID  SellerName    Phones  Recharges   SimCards  Total
1         Frank        2        2           1         5
2         Sally        2        0           2         4

Related Solutions

SQL Server – How to Return Rows One at a Time from Variable Table

This query should do what you want and avoid any issues with 1 to many relationships messing up the SUMs

SELECT i.job_no, installment_billing_amount - transaction_billing_amount
FROM (
        SELECT job_no, SUM(jm_installment.billing_amount) installment_billing_amount 
        FROM [FCU_LIVE].[dbo].[jm_installment] 
        GROUP BY job_no
    ) i
    INNER JOIN (
        SELECT jm_work_order.job_no, SUM(jm_wo_transaction.billing_amount) transaction_billing_amount
        FROM [FCU_LIVE].[dbo].[jm_work_order]
            INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
                AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
                AND jm_wo_transaction.override = 'N'
        GROUP BY jm_work_order.job_no
    ) t ON i.job_no = t.job_no
WHERE   i.job_no IN ( SELECT    job_no
                      FROM      @job_nos )

It is easier to follow as a CTE query.

WITH 
    installmentSum AS 
    (  
    SELECT job_no, SUM(jm_installment.billing_amount) installment_billing_amount 
    FROM [FCU_LIVE].[dbo].[jm_installment] 
    GROUP BY job_no
    )
    ,
    transactionSum AS 
    (
    SELECT jm_work_order.job_no, SUM(jm_wo_transaction.billing_amount) transaction_billing_amount
    FROM [FCU_LIVE].[dbo].[jm_work_order]
        INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
            AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
    WHERE jm_wo_transaction.override = 'N'
    GROUP BY jm_work_order.job_no
    )
SELECT j.job_no, installment_billing_amount - transaction_billing_amount
FROM @job_nos j
    INNER JOIN installmentSum i ON j.job_no = i.job_no
    INNER JOIN transactionSum t ON i.job_no = t.job_no;

MySQL – Multi-Table Sum of Cost Breakdown by Pricing Category

Unfortunately, MySQL is lacking window or analytic functions which make complex grouping queries easier. For your use case, though, it should be possible to get it working with a single query.

Doing a simple row-based group of your sample dataset:

MySQL [test]> select orderId , category, count(*), sum(subtotal) from OrderLineItems group by 1,2;
+---------+-----------+----------+---------------+
| orderId | category  | count(*) | sum(subtotal) |
+---------+-----------+----------+---------------+
|  323607 | BUILD     |        5 |       2515.00 |
|  323607 | FINISHING |        1 |         30.00 |
|  323607 | SETUP     |        2 |         95.00 |
|  323614 | BUILD     |        4 |       4614.00 |
|  323614 | SETUP     |        1 |         36.00 |
|  323636 | BUILD     |        2 |        840.00 |
|  323636 | FINISHING |        1 |        130.00 |
|  323636 | SETUP     |        7 |       1152.00 |
|  323649 | BUILD     |        1 |        240.00 |
|  323649 | FINISHING |        1 |         30.00 |
|  323649 | SETUP     |        4 |       1995.00 |
+---------+-----------+----------+---------------+
11 rows in set (0.00 sec)

You can pivot the subtotals to columns using the trick of using an IF statement in the select. This way you get the main part of your data with only one query:

SELECT orderId , count(*), 
sum(if(category='BUILD', subtotal, 0)) as Build_Total,
sum(if(category='SETUP', subtotal, 0)) as Setup_Total,
sum(if(category='FINISHING', subtotal, 0)) as Finishing_Total,
sum(subtotal) as Total
from OrderLineItems
GROUP BY 1;

With output:

+---------+----------+-------------+-------------+-----------------+---------+
| orderId | count(*) | Build_Total | Setup_Total | Finishing_Total | Total   |
+---------+----------+-------------+-------------+-----------------+---------+
|  323607 |        8 |     2515.00 |       95.00 |           30.00 | 2640.00 |
|  323614 |        5 |     4614.00 |       36.00 |            0.00 | 4650.00 |
|  323636 |       10 |      840.00 |     1152.00 |          130.00 | 2122.00 |
|  323649 |        6 |      240.00 |     1995.00 |           30.00 | 2265.00 |
+---------+----------+-------------+-------------+-----------------+---------+
4 rows in set (0.00 sec)

You can use the same approach to get your date-based rollups.

Best Answer

Related Solutions

SQL Server – How to Return Rows One at a Time from Variable Table

MySQL – Multi-Table Sum of Cost Breakdown by Pricing Category

Related Question