Koda Koda - 1 month ago 10
SQL Question

MS SQL - List calculated result in a row

This is the result goal

[AVAILABLE WHITE] [AVAILABLE BLUE] [AVAILABLE GREEN] [TOTAL AVAILABLE]
4 5 3 12


to list the calculated values in one row as shown.
Does anyone know how this is written correctly in Microsoft SQL?

This is my query, hoping that the + would concatenate each statement next to the previous one, but it did not work:

SELECT SUM(Num_Avail) FROM Goods
WHERE Color = 'White'
+
SELECT SUM(Num_Avail) AS [Available Blue] FROM Goods
WHERE Color = 'Blue'
+
SELECT SUM(Num_Avail) AS [Available Green] FROM Goods
WHERE Color = 'Green'
SELECT SUM(Num_Avail) AS [Total Available] FROM Goods
WHERE Color = 'Blue'or Color ='White'


Here is the table source
Create table Goods(

GoodsCode int not null,
ItemCode varchar(255),
Num_Avail int,
Color varchar(255)
);

INSERT INTO Goods(GoodsCode,ItemCode,Num_Avail,Color) values(1,'ABC',5,'Blue');
INSERT INTO Goods(GoodsCode,ItemCode,Num_Avail,Color) values(2,'ABC',2,'White');
INSERT INTO Goods(GoodsCode,ItemCode,Num_Avail,Color) values(3,'DEF',2,'White');
INSERT INTO Goods(GoodsCode,ItemCode,Num_Avail,Color) values(4,'DEF',3,'Green');

Answer

Conditional aggregation should do the trick

SELECT SUM(CASE WHEN Color = 'White' THEN Num_Avail ELSE 0 END) as  [AVAILABLE WHITE],
SUM(CASE WHEN Color = 'Blue' THEN Num_Avail ELSE 0 END) as  [AVAILABLE BLUE],
SUM(CASE WHEN Color = 'Green' THEN Num_Avail ELSE 0 END) as  [AVAILABLE GREEN],
SUM( Num_Avail ) as  [AVAILABLE TOTAL]
FROM Goods
Comments