How to properly add an additional column in a SELECT statement in MySQL?

Question

I would like to extract the number of attendances (i.e., COUNT()) of "Coaches" at "Shows" happening during two separate months: March and April. I managed to create a query that collects that number over only one of the months. In addition, via slightly modifying the query, the numbers over the second month can be found easily. But how do I merge them into one table containing both columns?

So, given the two queries and resulting tables below, how would one "append" the result of Query 2 to the result of Query 1? In other words, how would one combine their respective SELECT statements?

I included links to the SQL fiddle in case you need them.

Thank you in advance.

SQL Fiddle

Query 1:

SELECT C.*, COUNT(CIS.idCoach) AS MarchNumOfShows
FROM Coach AS C
  LEFT JOIN 
    (
        CoachInShow AS CIS
     LEFT JOIN
        TVShow AS S
     ON S.idShow = CIS.idShow
    )
  ON C.idCoach = CIS.idCoach AND S.airDate LIKE '_____04___'
  GROUP BY C.idCoach

Results:

| idCoach |      name | surname | MarchNumOfShows |
|---------|-----------|---------|-----------------|
|       1 |   Stephen | Hawking |               5 |
|       2 |  Nicholas |    Cage |               7 |
|       3 | Sigourney |  Weaver |               6 |

Query 2 (Minimal difference, querying for April instead of March):

SELECT COUNT(CIS.idCoach) AS AprilNumOfShows
FROM Coach AS C
  LEFT JOIN 
    (
        CoachInShow AS CIS
     LEFT JOIN
        TVShow AS S
     ON S.idShow = CIS.idShow
    )
  ON C.idCoach = CIS.idCoach AND S.airDate LIKE '_____05___'
  GROUP BY C.idCoach

Results:

| AprilNumOfShows |
|-----------------|
|               8 |
|               7 |
|              10 |

Wanted:

| idCoach |      name | surname | MarchNumOfShows | AprilNumOfShows |
|---------|-----------|---------|-----------------|-----------------|
|       1 |   Stephen | Hawking |               5 |               8 |
|       2 |  Nicholas |    Cage |               7 |               7 |
|       3 | Sigourney |  Weaver |               6 |              10 |

please tag your DBMS as well.

Vamsi Prabhala
– Vamsi Prabhala

2018-02-27 19:00:14 +00:00
Commented Feb 27, 2018 at 19:00 — Vamsi Prabhala
– Vamsi Prabhala, Commented Feb 27, 2018 at 19:00

Sphinx · Accepted Answer · 2018-02-27 19:29:32Z

1

You are very close, the last step you missed is simply combine MarchNumOfShows and AprilNumOfShows with left join.

like below codes (or look into the Sql Fiddle ):

SELECT C.idCoach, C.name, C.surname, COUNT(distinct CIS4.idShow) AS MarchNumOfShows
, COUNT(distinct CIS5.idShow) AS AprilNumOfShows
FROM Coach AS C
  LEFT JOIN 
    (
        CoachInShow AS CIS4
     LEFT JOIN
        TVShow AS S4
     ON S4.idShow = CIS4.idShow
    )
  ON C.idCoach = CIS4.idCoach AND S4.airDate LIKE '_____04___'
  LEFT JOIN 
    (
        CoachInShow AS CIS5
     LEFT JOIN
        TVShow AS S5
     ON S5.idShow = CIS5.idShow
    )
  ON C.idCoach = CIS5.idCoach AND S5.airDate LIKE '_____05___'
  GROUP BY C.idCoach;

And below is another way to get the same output (or look into SQL Fiddle):

SELECT C.idCoach, C.name, C.surname, 
  sum(case when DATE_FORMAT(airDate,'%M')='April' then 1 else null end ) AS AprilNumOfShows, 
  sum(case when DATE_FORMAT(airDate,'%M')='May' then 1 else null end ) AS MayNumOfShows
FROM Coach AS C
  LEFT JOIN 
    (
        CoachInShow AS CIS
     LEFT JOIN
        TVShow AS S
     ON S.idShow = CIS.idShow
    )
  ON C.idCoach = CIS.idCoach 
  GROUP BY C.idCoach;

edited Feb 27, 2018 at 19:29

answered Feb 27, 2018 at 19:12

Sphinx

10.7k2 gold badges36 silver badges50 bronze badges

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

2 Comments

khosthos Over a year ago

I thought so too, but I had doubt of whether there was a way to do it without a lot of code duplication. Thank you kind sir/lady!

Sphinx Over a year ago

@DavidSimonTetruashvili, perhaps the second solution is what you are expecting

Z . · Accepted Answer · 2018-02-27 19:04:48Z

0

one way to do it is with a case:

select *,
  sum(case when airdate like "%03%" then 1 else 0 end) as March,
  sum(case when airdate like "%04%" then 1 else 0 end) as April
  ...

answered Feb 27, 2018 at 19:04

Z .

12.8k2 gold badges34 silver badges59 bronze badges

Collectives™ on Stack Overflow

How to properly add an additional column in a SELECT statement in MySQL?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related