CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rafta
Rafta

Reputation: 23

Case statement with grouping

Can you help me write a SELECT statement that returns a single row having columns for each TypeId involved for the transaction number 55?

CREATE TABLE Types
(
Id INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(100) NOT NULL,
CONSTRAINT PK_Types PRIMARY KEY CLUSTERED(Id)
)
GO

INSERT INTO Types
VALUES
 ('Type1')
,('Type2')
,('Type3')
,('Type4')
,('Type5')
GO

CREATE TABLE Transactions
(
Id INT IDENTITY(1000,1) NOT NULL,
TypeId INT NULL,
TransactionNumber INT NOT NULL,
Amount MONEY NULL,
DateRecorded DATETIME2 NOT NULL,
CONSTRAINT PK_Transactions PRIMARY KEY CLUSTERED(Id),
CONSTRAINT FK_Types FOREIGN KEY(TypeId) REFERENCES Types(Id)
)
GO

INSERT INTO Transactions
VALUES
 (1,55,2555.50,SYSDATETIME())
,(3,55,3555.50,SYSDATETIME())
,(4,55,4555.50,SYSDATETIME())
,(5,55,5555.50,SYSDATETIME())
GO

I need a single row returned for each transaction number in the Transactions table.

What I already tried:

SELECT TransactionNumber
    ,(CASE WHEN TypeId = 1 THEN Amount ELSE 0 END) AS Type1
    ,(CASE WHEN TypeId = 2 THEN Amount ELSE 0 END) AS Type2
    ,(CASE WHEN TypeId = 3 THEN Amount ELSE 0 END) AS Type3
    ,(CASE WHEN TypeId = 4 THEN Amount ELSE 0 END) AS Type4
    ,(CASE WHEN TypeId = 5 THEN Amount ELSE 0 END) AS Type5
FROM Transactions

Upvotes: 2

Views: 104

Answers (4)

tjeloep
tjeloep

Reputation: 318

SELECT TransactionNumber
    ,sum(CASE WHEN TypeId = 1 THEN Amount ELSE 0 END) AS Type1
    ,sum(CASE WHEN TypeId = 2 THEN Amount ELSE 0 END) AS Type2
    ,sum(CASE WHEN TypeId = 3 THEN Amount ELSE 0 END) AS Type3
    ,sum(CASE WHEN TypeId = 4 THEN Amount ELSE 0 END) AS Type4
    ,sum(CASE WHEN TypeId = 5 THEN Amount ELSE 0 END) AS Type5
FROM Transactions group by TransactionNumber;

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

I think this would be the query you are looking for:

SELECT 
    COUNT(CASE WHEN TypeID = 1 THEN 1 ELSE NULL END) '# Transactions Type 1'
    , COUNT(CASE WHEN TypeID = 2 THEN 1 ELSE NULL END) '# Transactions Type 2'
    , COUNT(CASE WHEN TypeID = 3 THEN 1 ELSE NULL END) '# Transactions Type 3'
    , COUNT(CASE WHEN TypeID = 4 THEN 1 ELSE NULL END) '# Transactions Type 4'
    , COUNT(CASE WHEN TypeID = 5 THEN 1 ELSE NULL END) '# Transactions Type 5'
FROM TRANSACTIONS
Where TransactionNumber = 55

You can find a working SQLFiddle here.

EDIT:

After you comment I think what you are actually looking for is SUM().

SELECT TransactionNumber
    , SUM(CASE WHEN TypeId = 1 THEN Amount ELSE 0 END) AS Type1
    , SUM(CASE WHEN TypeId = 2 THEN Amount ELSE 0 END) AS Type2
    , SUM(CASE WHEN TypeId = 3 THEN Amount ELSE 0 END) AS Type3
    , SUM(CASE WHEN TypeId = 4 THEN Amount ELSE 0 END) AS Type4
    , SUM(CASE WHEN TypeId = 5 THEN Amount ELSE 0 END) AS Type5
FROM Transactions
GROUP BY TransactionNumber

SQLFiddle for 2nd query here.

Upvotes: 0

Rohit Vipin Mathews
Rohit Vipin Mathews

Reputation: 11787

I think you can do a PIVOT to get required output:

SELECT *
FROM (
    SELECT ty.NAME, ISNULL(Amount,0)Amount
    FROM TRANSACTIONS t
    INNER JOIN Types ty ON ty.Id = t.TypeId
    WHERE TransactionNumber = 55
    ) sub
PIVOT(
      SUM(Amount) FOR [Name] IN ([Type1], [Type2], [Type3], [Type4], [Type5])
     ) AS pivottable

SQL Fiddle

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

With conditional aggregation:

select Id,
       sum(case TypeId when 1 then Amount end) Type1,
       sum(case TypeId when 2 then Amount end) Type2,
       ...
from Transactions
group by Id

Upvotes: 0

Related Questions