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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.