1

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?

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.