Koda Koda - 1 month ago 7
SQL Question

SQL Server : 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 Server?

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