CODEWITHSUNDEEP
mysqlsqlmariadb
khosthos
khosthos

Reputation: 133

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

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 |

Upvotes: 1

Views: 111

Answers (2)

Sphinx
Sphinx

Reputation: 10729

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;

Upvotes: 1

Z .
Z .

Reputation: 12837

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
  ...

Upvotes: 0

Related Questions