Adding UNION to a complex SELECT in MySQL

Question

I would like help adding a UNION to get all the months to a query. I tried adding union in a bunch of spots but just can't get it right.

SELECT MONTH(FirstPublishedToPortal) AS theMonth,
   YEAR(FirstPublishedToPortal) AS theYear,
   MONTHNAME(FirstPublishedToPortal) AS theMonthName,
   COUNT(DISTINCT PONum) AS POCount,

   SUM(unconfirmedEmailSent) AS unconfirmedEmailsSent,
   ( SELECT COUNT(DISTINCT PONum)
     FROM POFlags
     WHERE unconfirmedEmailSent = 0
     AND MONTH(FirstPublishedToPortal) = theMonth
     AND YEAR(FirstPublishedToPortal) = theYear
     AND VendorNum = '2222'
   ) AS onTimeConfirmed,

   SUM(lateEmailSent) AS lateEmailsSent,
   ( SELECT COUNT(DISTINCT PONum)
     FROM POFlags
     WHERE lateEmailSent = 0
     AND MONTH(FirstPublishedToPortal) = theMonth
     AND YEAR(FirstPublishedToPortal) = theYear
     AND VendorNum = '2222'
   ) AS onTimePOCount

FROM POFlags

WHERE VendorNum = '2222'
   AND FirstPublishedToPortal >= '2017-01-08'

GROUP BY theYear DESC, theMonth DESC
ORDER BY FirstPublishedToPortal DESC

Where do the union clauses go in this query? I think there needs to be something like the following code but I don't understand where to put it to work correctly with the GROUP BY or ORDER BY.

(SELECT 1, null, 'January', 0, 0, 0, 0, 0)
UNION 
(SELECT 2, null, 'February', 0, 0, 0, 0, 0)
UNION 
(SELECT 3, null, 'March', 0, 0, 0, 0, 0)
.    
.
(SELECT 12, null, 'December, 0, 0, 0, 0, 0)

SQL FIDDLE HERE

Indent · Accepted Answer · 2017-12-11 19:08:27Z

2

Use a left join based on a nested query with union.

Use Coalesce to apply your default values.

Move where condition on join clause

warning, 2017 is hardcoded

Something like this : http://sqlfiddle.com/#!9/458808/27

SELECT
    all_month.nMonth AS theMonth, 
    coalesce(YEAR(FirstPublishedToPortal),2017) AS theYear,
    all_month.sMonth AS theMonthName,
    coalesce(COUNT(DISTINCT PONum),0) AS POCount,

    coalesce(SUM(unconfirmedEmailSent),0) AS unconfirmedEmailsSent,
    coalesce(( SELECT COUNT(DISTINCT PONum)
       FROM POFlags
       WHERE unconfirmedEmailSent = 0
         AND MONTH(FirstPublishedToPortal) = theMonth
         AND YEAR(FirstPublishedToPortal) = theYear
         AND VendorNum = '2222'
    ),0) AS onTimeConfirmed,

    coalesce(SUM(lateEmailSent),0) AS lateEmailsSent,
    coalesce(( SELECT COUNT(DISTINCT PONum)
       FROM POFlags
       WHERE lateEmailSent = 0
         AND MONTH(FirstPublishedToPortal) = theMonth
         AND YEAR(FirstPublishedToPortal) = theYear
         AND VendorNum = '2222'
     ),0) AS onTimePOCount
FROM (
     (SELECT 1 nMonth, 'January' sMonth)   UNION ALL
     (SELECT 2 nMonth, 'February' sMonth ) UNION ALL
     (SELECT 3 nMonth, 'March' sMonth )    UNION ALL
     (SELECT 4 nMonth, 'April' sMonth )    UNION ALL
     (SELECT 5 nMonth, 'May' sMonth )      UNION ALL
     (SELECT 6 nMonth, 'June' sMonth )     UNION ALL
     (SELECT 7 nMonth, 'July' sMonth )     UNION ALL
     (SELECT 8 nMonth, 'August' sMonth )   UNION ALL
     (SELECT 9 nMonth, 'September' sMonth) UNION ALL
     (SELECT 10 nMonth, 'October' sMonth ) UNION ALL
     (SELECT 11 nMonth, 'November' sMonth )UNION ALL    
     (SELECT 12 nMonth, 'December' sMonth)
     ) all_month left join POFlags on
         MONTH(FirstPublishedToPortal)=nMonth and
         VendorNum = '2222' and
         FirstPublishedToPortal >= '2017-01-08'
GROUP BY
    theYear DESC, nMonth DESC
ORDER BY
    nMonth DESC

Another way exist using a global union between your orginal result and the default value, but this method require a second group by...

edited Dec 11, 2017 at 19:08

answered Dec 11, 2017 at 17:02

Indent

4,9671 gold badge22 silver badges39 bronze badges

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

3 Comments

S.Mason Over a year ago

This works wonderfully. I had trouble understanding where to put the UNION selects. And using COALESCE for default values--I never thought of that! Since I can build this query at run time, I can pass the year based on some parameter passed to my PHP script-- so no problem there. Thank you!

S.Mason Over a year ago

How I solved the default hardcoded year problem since I only needed one year's worth of data and had some variables available in my script:

COALESCE(YEAR(FirstPublishedToPortal), IF(nMonth > " . $cutoff_month . ", " . $prev_year . ", " . $cutoff_year . ") )  AS theYear,

Indent Over a year ago

Using PHP, it's a simple solution ;-). With SQL only it's a little more complexe.

Collectives™ on Stack Overflow

Adding UNION to a complex SELECT in MySQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related