How can I combine two SQL queries returning multiple columns into separate columns of the same table?

Question

I would like to combine the following SQL queries to return a single table with the column names:

mnth, correct_predictions, incorrect_predictions

ts is a timestamp type.

SELECT monthname(ts) as mnth, count(*) AS correct_predictions
FROM prediction 
WHERE actual = 1 AND result = 1 OR actual = 0 AND result = 0
GROUP BY monthname(ts);

SELECT monthname(ts) as mnth, count(*) AS incorrect_predictions
FROM prediction 
WHERE actual = 0 AND result = 1 OR actual = 1 AND result = 0
GROUP BY monthname(ts);

Nick · Accepted Answer · 2020-05-07 12:26:21Z

3

Since MySQL treats booleans as 1 or 0 in a numeric context, you can just SUM the result of comparing actual and result to get your required columns:

SELECT MONTHNAME(ts) as mnth, 
       SUM(actual = result) AS correct_predictions,
       SUM(actual != result) AS incorrect_predictions
FROM prediction 
GROUP BY mnth;

Note that using MONTHNAME will result in values from every year being grouped together (e.g. May 2019 with May 2020). That may be what you want (or perhaps you are restricting the range of the query with a WHERE clause) but if not, you should use something like

EXTRACT(YEAR_MONTH FROM ts) AS mnth

to include the year in the value you are grouping by.

edited May 7, 2020 at 12:26

answered May 7, 2020 at 7:28

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gordon Linoff Over a year ago

@mrrain . . . This is actually much simpler. I would, however, advise you to include the year as well as the month name in the select and group by.

Nick Over a year ago

@GordonLinoff good point about the year, I've made a note about it in the answer.

zealous Over a year ago

@Nick very neat solution! :)

zealous · Accepted Answer · 2020-05-07 06:57:57Z

1

Try the following with case

SELECT 
    monthname(ts) as mnth,
    sum(case
            when (actual = 1 AND result = 1) OR (actual = 0 AND result = 0) then 1 else 0 
        end) 
   AS correct_predictions,
    sum(case
            when (actual = 0 AND result = 1) OR (actual = 1 AND result = 0) then 1 else 0 
        end) 
   AS incorrect_predictions
FROM prediction 
GROUP BY monthname(ts);

answered May 7, 2020 at 6:57

zealous

7,5434 gold badges20 silver badges39 bronze badges

Collectives™ on Stack Overflow

How can I combine two SQL queries returning multiple columns into separate columns of the same table?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related