1

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.

1 Answer 1

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

1
  • Perfect! Thanks, I think I was overcomplicating things Feb 3, 2023 at 13:44

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .