?
Solved

need to get counts even if there are zero

Posted on 2013-11-11
2
Medium Priority
?
270 Views
Last Modified: 2013-11-17
I have the following query - I need to be able to get a count for EACH 'nextDueDateRange' even if the count is zero

So in addition to listing things as they are (each individual row) I need tally's and have just not been able to figure it out
SELECT
      CASE  
         WHEN a.nextDueDate < CURDATE() THEN 'Overdue' 
         WHEN a.nextDueDate = CURDATE() THEN 'Today'
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 7 DAY) )THEN '7 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 14 DAY) )THEN '15 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 30 DAY) )THEN '30 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 60 DAY) )THEN '60 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 90 DAY) )THEN '90 Days'
         ELSE 'Greater Than 90 Days' 
       END AS nextDueDateRange,
     g.groupId,g.name AS groupName,l.logId,l.name AS logName, 
     g1.parentId AS parentId1, g1.name AS group1Name,g2.parentId AS parentId2, g2.name AS group2Name,
     g3.parentId AS parentId3, g3.name AS group3Name,
     a.name AS activityName,a.scheduleType,a.scheduleInterval,a.details,nextDueDate
       
  FROM activities a
      INNER JOIN logs l ON l.logID = a.logId
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId
    WHERE nextDueDate IS NOT NULL

Open in new window

0
Comment
Question by:portlight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39640690
MySQL does not support CTE. Therefore you need to use a workaround. You need to use this small view as a subquery JOINED with your main query:
SELECT 'Overdue' AS nextDueDateRange
UNION ALL
SELECT 'Overdue' AS nextDueDateRange
UNION ALL
SELECT 'Today' AS nextDueDateRange
UNION ALL
SELECT '7 Days' AS nextDueDateRange
UNION ALL
SELECT '15 Days' AS nextDueDateRange
UNION ALL
SELECT '30 Days' AS nextDueDateRange
UNION ALL
SELECT '60 Days' AS nextDueDateRange
UNION ALL
SELECT '90 Days' AS nextDueDateRange
UNION ALL
SELECT 'Greater Than 90 Days' AS nextDueDateRange

Open in new window

So, the resulting query will look like this:
SELECT r.nextDueDateRange,
a.groupId,
a.groupName,
a.logId,
a.logName, 
a.parentId1,
a.group1Name,
a.parentId2, 
a.group2Name,
a.parentId3, 
a.group3Name,
a.activityName,
a.scheduleType,
a.scheduleInterval,
a.details,
a.nextDueDate
  FROM (
SELECT 'Overdue' AS nextDueDateRange
UNION ALL
SELECT 'Overdue' AS nextDueDateRange
UNION ALL
SELECT 'Today' AS nextDueDateRange
UNION ALL
SELECT '7 Days' AS nextDueDateRange
UNION ALL
SELECT '15 Days' AS nextDueDateRange
UNION ALL
SELECT '30 Days' AS nextDueDateRange
UNION ALL
SELECT '60 Days' AS nextDueDateRange
UNION ALL
SELECT '90 Days' AS nextDueDateRange
UNION ALL
SELECT 'Greater Than 90 Days' AS nextDueDateRange
) AS r
 LEFT JOIN 
(
SELECT
      CASE  
         WHEN a.nextDueDate < CURDATE() THEN 'Overdue' 
         WHEN a.nextDueDate = CURDATE() THEN 'Today'
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 7 DAY) )THEN '7 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 14 DAY) )THEN '15 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 30 DAY) )THEN '30 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 60 DAY) )THEN '60 Days' 
         WHEN a.nextDueDate > CURDATE() 
              AND a.nextDueDate < ( DATE_ADD(CURDATE(), INTERVAL 90 DAY) )THEN '90 Days'
         ELSE 'Greater Than 90 Days' 
       END AS nextDueDateRange,
     g.groupId,g.name AS groupName,l.logId,l.name AS logName, 
     g1.parentId AS parentId1, g1.name AS group1Name,g2.parentId AS parentId2, g2.name AS group2Name,
     g3.parentId AS parentId3, g3.name AS group3Name,
     a.name AS activityName,a.scheduleType,a.scheduleInterval,a.details,nextDueDate
       
  FROM activities a
      INNER JOIN logs l ON l.logID = a.logId
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId
    WHERE nextDueDate IS NOT NULL) a ON
a.nextDueDateRange = r.nextDueDateRange

Open in new window

A bit complex, but not scary
0
 

Author Closing Comment

by:portlight
ID: 39654720
This worked PERECTLY! Thank you
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question