MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
create table tab2 (date datetime)
insert into tab2 values ('20120101')
insert into tab2 values ('20120201')
insert into tab2 values ('20120301')
insert into tab2 values ('20120401')
insert into tab2 values ('20120501')
insert into tab2 values ('20120601')
insert into tab2 values ('20120701')
insert into tab2 values ('20120801')
insert into tab2 values ('20120901')
insert into tab2 values ('20121001')
insert into tab2 values ('20121101')
insert into tab2 values ('20121201')
select datename(MM, date) as month_name
from tab2
union
select datename(MM, date) as month_name
from tab2
order by datepart(mm, [date])
select datename(MM, month_name) as month_name
from
(
select date as month_name
from tab2
union
select date as month_name
from tab2 ) as a
order by datepart(mm, [month_name])
SELECT DATENAME(MM,[SubmissionDate]) as MonthName
,DATEPART(YEAR,[SubmissionDate]) as YearName
,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END) Riyadh
,SUM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END) Jeddah
,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END) Dammam
,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) as Total
FROM [STR].[dbo].[ILicensesRenewApplications]R
WHERE StatusID <> 1
Group by DATENAME(MM,[SubmissionDate]),DATEPART(YEAR,[SubmissionDate])
UNION
SELECT 'Sum' MonthName
,' ' YearName
,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END) Riyadh
,suM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END) Jeddah
,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END) Dammam
,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) as Total
FROM [STR].[dbo].[ILicensesRenewApplications]R
WHERE StatusID <> 1
order by case MonthName
when 'January' then 1
when 'February' then 2
when 'March' then 3
when 'April' then 4
when 'May' then 5
when 'June' then 6
when 'July' then 7
when 'August' then 8
when 'September' then 9
when 'October' then 10
when 'November' then 11
when 'December' then 12
else 13
end,
YearName
SELECT *
FROM
(
SELECT DATENAME(MM,[SubmissionDate]) as MonthName
,DATEPART(YEAR,[SubmissionDate]) as YearName
,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END) Riyadh
,SUM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END) Jeddah
,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END) Dammam
,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) as Total
FROM [STR].[dbo].[ILicensesRenewApplications]R
WHERE StatusID <> 1
Group by DATENAME(MM,[SubmissionDate]),DATEPART(YEAR,[SubmissionDate])
UNION
SELECT 'Sum' MonthName
,' ' YearName
,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END) Riyadh
,suM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END) Jeddah
,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END) Dammam
,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) as Total
FROM [STR].[dbo].[ILicensesRenewApplications]R
WHERE StatusID <> 1
) A
order by case MonthName
when 'January' then 1
when 'February' then 2
when 'March' then 3
when 'April' then 4
when 'May' then 5
when 'June' then 6
when 'July' then 7
when 'August' then 8
when 'September' then 9
when 'October' then 10
when 'November' then 11
when 'December' then 12
else 13
end,
YearName
SELECT MonthName,
YearName,
Riyadh,
Jeddah,
Dammam,
Total
FROM (SELECT DATEPART(YEAR, [SubmissionDate]) AS YearName,
DATEPART(MM, [SubmissionDate]) MonthNumber,
DATENAME(MM, [SubmissionDate]) AS MonthName,
SUM(CASE R.BranchID
WHEN 1 THEN 1
ELSE 0
END) Riyadh,
SUM(CASE R.BranchID
WHEN 2 THEN 1
ELSE 0
END) Jeddah,
SUM(CASE R.BranchID
WHEN 3 THEN 1
ELSE 0
END) Dammam,
SUM(CASE WHEN R.BranchID = '1'
OR R.BranchID = '2'
OR R.BranchID = '3' THEN 1
ELSE 0
END) AS Total
FROM [STR].[dbo].[ILicensesRenewApplications] R
WHERE StatusID <> 1
GROUP BY DATEPART(year, [SubmissionDate]),
DATEPART(month, [SubmissionDate]),
DATENAME(month, [SubmissionDate])
UNION ALL
SELECT 9999 YearName,
13,
'Sum' MonthName,
SUM(CASE R.BranchID
WHEN 1 THEN 1
ELSE 0
END) Riyadh,
SUM(CASE R.BranchID
WHEN 2 THEN 1
ELSE 0
END) Jeddah,
SUM(CASE R.BranchID
WHEN 3 THEN 1
ELSE 0
END) Dammam,
SUM(CASE WHEN R.BranchID = '1'
OR R.BranchID = '2'
OR R.BranchID = '3' THEN 1
ELSE 0
END) AS Total
FROM [STR].[dbo].[ILicensesRenewApplications] R
WHERE StatusID <> 1
) x
ORDER BY YearName,
MonthNumber
SELECT DATENAME(MM,[SubmissionDate]) as MonthName
,DATEPART(YEAR,[SubmissionDate]) as YearName
,Riyadh
,Jeddah
,Dammam
,Total
FROM
(
SELECT [SubmissionDate] as MonthName
,[SubmissionDate] as YearName
,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END) Riyadh
,SUM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END) Jeddah
,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END) Dammam
,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) as Total
FROM [STR].[dbo].[ILicensesRenewApplications]R
WHERE StatusID <> 1
Group by DATENAME(MM,[SubmissionDate]),DATEPART(YEAR,[SubmissionDate])
UNION
SELECT 'Sum' MonthName
,' ' YearName
,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END) Riyadh
,suM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END) Jeddah
,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END) Dammam
,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) as Total
FROM [STR].[dbo].[ILicensesRenewApplications]R
WHERE StatusID <> 1
) as a
order by DATEPART(MM,[SubmissionDate])
,DATEPART(YEAR,[SubmissionDate])
order by
YearName,
case MonthName
when 'January' then 1
when 'February' then 2
when 'March' then 3
when 'April' then 4
when 'May' then 5
when 'June' then 6
when 'July' then 7
when 'August' then 8
when 'September' then 9
when 'October' then 10
when 'November' then 11
when 'December' then 12
else 13
end
Try it this way (notice how I also reversed the order of the year and month)
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.