Sort Months from January to December in SQL-CodePudding

I want to sort the month starting from January to December, however here's my case:

Here's my query for the above picture

SELECT  SUM(totalAmount) AS Sales, 'January' AS NameMonth
FROM  sales_tbl
WHERE MONTH(transactionDate) = 1
UNION
SELECT  SUM(totalAmount) AS Sales, 'February' AS NameMonth
FROM  sales_tbl
WHERE MONTH(transactionDate) = 2
UNION
SELECT  SUM(totalAmount) AS Sales, 'March' AS NameMonth
FROM  sales_tbl
WHERE MONTH(transactionDate) = 3
UNION
SELECT  SUM(totalAmount) AS Sales, 'April' AS NameMonth
FROM  sales_tbl
WHERE MONTH(transactionDate) = 4
UNION
SELECT  SUM(totalAmount) AS Sales, 'May' AS NameMonth
FROM  sales_tbl
WHERE MONTH(transactionDate) = 5
UNION
SELECT  SUM(totalAmount) AS Sales, 'June' AS NameMonth
FROM  sales_tbl
WHERE MONTH(transactionDate) = 6
UNION
SELECT  SUM(totalAmount) AS Sales, 'July' AS NameMonth
FROM  sales_tbl
WHERE MONTH(transactionDate) = 7
UNION
SELECT  SUM(totalAmount) AS Sales, 'August' AS NameMonth
FROM  sales_tbl
WHERE MONTH(transactionDate) = 8
UNION
SELECT  SUM(totalAmount) AS Sales, 'September' AS NameMonth
FROM  sales_tbl
WHERE MONTH(transactionDate) = 9
UNION
SELECT  SUM(totalAmount) AS Sales, 'October' AS NameMonth
FROM  sales_tbl
WHERE MONTH(transactionDate) = 10
UNION
SELECT  SUM(totalAmount) AS Sales, 'November' AS NameMonth
FROM  sales_tbl
WHERE MONTH(transactionDate) = 11
UNION
SELECT  SUM(totalAmount) AS Sales, 'December' AS NameMonth
FROM  sales_tbl
WHERE MONTH(transactionDate) = 12

I tried to include this in the query to solve my problem:

ORDER BY MONTH(NameMonth)

But, I got an error Error Message

Originally, NameMonth is not a column in the table.

Everyone's answer is so much appreciated.

CodePudding user response：

First, use the following method to show you the sales output in one line per month. After that, you can use the Case When command to get the name of the month and perform the sort based on the ID of month (MonthID or MONTH(transactionDate)). try follow:

SELECT  SUM(totalAmount) AS Sales , MONTH(transactionDate) AS MonthID
FROM  sales_tbl
GROUP BY MONTH(transactionDate)

CodePudding user response：

You can do this.

select DateName( month , DateAdd( month , datepart(Month, transactionDate) , -1 ) ) AS Sales, sum(totalAmount) As NameMonth from Mas_Transaction
group by datepart(Month, transactionDate)
order by datepart(Month, transactionDate)