How to add a column to an existing SQL output?

February 17, 2022

I am new to Transact-SQL, assume I have a sql table

SELECT 
    Foods,
    Count(*) as total_count
FROM [SuperMarket].[dbo].[Grocery]
GROUP BY Foods
ORDER BY Foods DESC

output:

Foods	total_count
3	27
2	35
1	109
0	783

Is it possible to add an extra column in the existing sql output such as following. However, there is NO ‘Name’ in the original sql db.

Foods	Name	total_count
3	meat	27
2	fish	35
1	fruit	109
0	pasta	783

many thanks

>Solution :

You can use a case statement:

SELECT 
    Foods,
    CASE
    WHEN FOODS = 3 THEN 'meat'
    WHEN FOODS = 2 THEN 'fish'
    WHEN FOODS = 1 THEN 'fruit'
    WHEN FOODS = 0 THEN 'pasta'
    END as Name,
    Count(*) as total_count
FROM [SuperMarket].[dbo].[Grocery]
GROUP BY Foods
ORDER BY Foods DESC