Combining Multiple queries from the same table inrto 1 query

Question

I am looking to find a way to achieve the below...

I have tried using Joins and SubQueries but no joy as yet...

I have a table that keeps track of wins/loses/draws as so

Date	WLD
01/01/23	Win
05/01/23	Win
12/01/23	Lose
30/01/23	Draw
01/02/23	Lose
02/02/23	Win
03/02/23	Win

Ideally, I want the query to group by month and give a count of wins, loses and draws and a total

Month	Win	Lose	Draw	Total
January	2	2	1	5
February	2	1	0	3

I have individial queries for each...

Total

SELECT COUNT(id) AS Total, MONTHNAME(DateTime) AS Month FROM Table
GROUP BY MONTH(STR_TO_DATE(Month, '%M'))
Order BY DateTime

Win

SELECT COUNT(id) AS Count, MONTHNAME(DateTime) AS Month FROM Table
Where WLE = 'Win'
GROUP BY MONTH(STR_TO_DATE(Month, '%M'))
Order BY DateTime

Lose

SELECT COUNT(id) AS Lose, MONTHNAME(DateTime) AS Month FROM Table
Where WLD = 'Lose'
GROUP BY MONTH(STR_TO_DATE(Month, '%M'))
Order BY DateTime

Draw

SELECT COUNT(id) AS Draw, MONTHNAME(DateTime) AS Month FROM Table
Where WLD = 'Draw'
GROUP BY MONTH(STR_TO_DATE(Month, '%M'))
Order BY DateTime

I am looking for a way to combine the above into the one query so that I can thenm plot a chart from the output.

Answer 1

Given data example and expected result doesn't match.

In the expected result there is a total of 5 per January , when in the given table there are 4 values per January.

As per your question.

MySQL supports conditional sum. For example for the win query simply use

sum(wld = 'Win') as Win 

It will return 1 per matching values and 0 for non matching.

Final query would be:

select MONTHNAME(date_) as 'Month',
       sum(wld = 'Win') as Win,
       sum(wld = 'Lose') as Lose,
       sum(wld = 'Draw') as Draw,
       count(*) as Total
from my_table
GROUP BY MONTHNAME(date_); 

Result:

Month     Win   Lose    Draw    Total
January    2     1       1       4
February   2     1       0       3

https://dbfiddle.uk/9fZQ_YVK