Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Google for PIVOT command –  Max Vernon Jun 27 at 18:05
    
I tried to adapt with examples from codeproject.com/Tips/500811/… but I could not understand because I did not found examples specific for use with ENUM. –  EASI Jun 27 at 18:30

1 Answer 1

up vote 3 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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