Return values even if null, 0 or not present

Question

I want to create some reports so I can grab data quickly regarding our helpdesk, I am using GLPI which is running on MySQL 5.5

I have the below code and it returns some useful information but not enough so it can be put into a pretty stacked graph which is what he has requested. To get enough data to achieve this I need the urgency's that are zero and the days that are also zero, then I can convert the data into a table in Excel and build the graph.

Current Query:

SELECT DATE_FORMAT(date,'%d/%m/%Y') AS Date,Urgency,COUNT(*) as Tickets 
FROM glpi.glpi_tickets
WHERE month(date)=month(NOW())
GROUP BY urgency ORDER BY date,urgency ASC;

This returns:

# Date, Urgency, Tickets
'07/06/2016', '3', '10'
'10/06/2016', '2', '1'
'14/06/2016', '1', '1'
'14/06/2016', '5', '1'

I would ideally like it to display as below:

# Date, Urgency, Tickets
'07/06/2016', '1', '0'
'07/06/2016', '2', '0'
'07/06/2016', '3', '10'
'07/06/2016', '4', '0'
'07/06/2016', '5', '0'
'08/06/2016', '1', '0'
'08/06/2016', '2', '0'
'08/06/2016', '3', '0'
'08/06/2016', '4', '0'
'08/06/2016', '5', '0'
...
'14/06/2016', '1', '1'
'14/06/2016', '2', '0'
'14/06/2016', '3', '0'
'14/06/2016', '4', '0'
'14/06/2016', '5', '1'

And so on.

I am kind of getting the hang of SQL (self teaching) so all and any help is much appreciated.

EDIT: Schema added, I think this is what you was asking for (hope it works) http://sqlfiddle.com/#!9/715c7

Answer 1

Your query produces one row per urgency because you only group by that column. In order to see distinct results for each date and urgency you must modify your GROUP BY.

SELECT 
    DATE_FORMAT(date,'%d/%m/%Y') AS Date,
    Urgency,
    COUNT(*) as Tickets 
FROM 
    glpi.glpi_tickets
WHERE 
    month(date)=month(NOW())
GROUP BY 
    DATE(date),
    urgency 
ORDER BY 
    date, urgency ASC;

Answer 2

I think this is what you are looking for. Though I did not have the schema, I tried to write the query. I think this is what you are looking for. (Please check the SQL, I have edited. It should generate a report like how you want, only missing records are, if there is no data for a date.)

    select DATE_FORMAT(dummy.date,'%d/%m/%Y') Date, dummy.Urgency, ifnull(main.Tickets, 0) Tickets  from 
(select * from 
(select distinct date(date) date from glpi_tickets
    WHERE month(date)=month(NOW()) ) dates
cross join 
(select distinct Urgency from glpi_tickets) urgency
order by Date, Urgency) dummy
left join 
(SELECT date(date) date, Urgency, COUNT(*) as Tickets 
FROM glpi_tickets
WHERE month(date)=month(NOW())
GROUP BY date(date), Urgency  
ORDER BY date(date), Urgency) main
on dummy.date = main.date
and dummy.Urgency = main.Urgency
order by dummy.date asc, Urgency asc

I guess the below SQL will serve your purpose, I took a little help from here. You need to optimize the query for big data.

select DATE_FORMAT(dummy.date,'%d/%m/%Y') Date, dummy.Urgency, ifnull(main.Tickets, 0) Tickets  from 
(select * from 
(SELECT date_field date
FROM
(SELECT
        MAKEDATE(YEAR(NOW()),1) +
        INTERVAL (MONTH(NOW())-1) MONTH +
        INTERVAL daynum DAY date_field
    FROM
    (SELECT t*10+u daynum
        FROM
            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
            UNION SELECT 8 UNION SELECT 9) B
        ORDER BY daynum
    ) AA
) AAA
WHERE MONTH(date_field) = MONTH(NOW())
and date_field >= (select min(date(date)) from glpi_tickets WHERE month(date)=month(NOW())) 
and date_field <= (select max(date(date)) from glpi_tickets WHERE month(date)=month(NOW())) ) dates
cross join 
(select distinct Urgency from glpi_tickets) urgency
order by date, Urgency) dummy
left join 
(SELECT date(date) date, Urgency, COUNT(*) as Tickets 
FROM glpi_tickets
WHERE month(date)=month(NOW())
GROUP BY date(date), Urgency  
ORDER BY date(date), Urgency) main
on dummy.date = main.date
and dummy.Urgency = main.Urgency
order by dummy.date asc, Urgency asc

Answer 3

So this is what I ended up doing and it appears to be supplying the data just how I want it.

First of all I created a new table to store the data in:

    CREATE TABLE glpi_plugin_ns_ticketstats
    (
    id INT(11),
    daterun date,
    timerun time,
    totaltickets INT(11),
    verylow INT(11),
    low INT(11),
    med INT(11),
    high INT(11),
    veryhigh INT(11));

Then I built a stored procedure to collect and populate the data:

USE `glpi`;
DROP procedure IF EXISTS `Daily_Ticket_Stats`;

DELIMITER $$
USE `glpi`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Daily_Ticket_Stats`()
BEGIN
declare todayd date;
## Declaring the variable for the daterun column ##
declare todayt time;
## Declaring the variable for the timerun column ##
declare totalt int(11);
## Declaring the variable for totaltickets column ##
declare vlow int (11);
## Declaring the variable for verylow column ##
declare low int(11);
## Declaring the variable for low column ##
declare med int(11);
## Declaring the variable for med column ##
declare high int (11);
## Declaring the variable for high column ##
declare vhigh int(11);
## Declaring the variable for veryhigh column ##
    set todayd = CURDATE();
    ## Set date to today ##
    set todayt = CURTIME();
    ## Set time to now ##
    set totalt = (SELECT COUNT(*) as ttickets 
        FROM glpi.glpi_tickets
    WHERE day(date)=day(NOW()));
    ## This has set the total for the total tickets variable ##
    set vlow = (SELECT COUNT(*) as vltickets 
        FROM glpi.glpi_tickets
    WHERE day(date)=day(NOW())
        AND urgency = '1');
    ## This has set the total for the very low urgency tickets variable ##
    set low = (SELECT COUNT(*) as ltickets 
        FROM glpi.glpi_tickets
    WHERE day(date)=day(NOW())
        AND urgency = '2');
    ## This has set the total for the low urgency tickets variable ##
    set med = (SELECT COUNT(*) as mtickets 
        FROM glpi.glpi_tickets
    WHERE day(date)=day(NOW())
        AND urgency = '3');
    ## This has set the total for the medium urgency tickets variable ##
    set high = (SELECT COUNT(*) as htickets 
        FROM glpi.glpi_tickets
    WHERE day(date)=day(NOW())
        AND urgency = '4');
    ## This has set the total for the high urgency tickets variable ##
    set vhigh = (SELECT COUNT(*) as vhtickets 
        FROM glpi.glpi_tickets
    WHERE day(date)=day(NOW())
        AND urgency = '5');

IF EXISTS(
    SELECT *
    FROM glpi.glpi_plugin_ns_ticketstats 
    WHERE daterun = CURDATE())
    THEN
BEGIN
    UPDATE glpi.glpi_plugin_ns_ticketstats 
SET 
    timerun = CURTIME(),
    totaltickets = totalt,
    verylow = vlow,
    low = low,
    med = med,
    high = high,
    veryhigh = vhigh
WHERE
    daterun = CURDATE();
END;
ELSE
INSERT INTO glpi.glpi_plugin_ns_ticketstats VALUES (NULL,todayd,todayt,totalt,vlow,low,med,high,vhigh);
END IF;
END
#$$

DELIMITER ;

I then set this procedure to run every hour so the stats are recent if my manager wishes to refer to them during the day (I entered this directly onto the mysql server terminal):

CREATE EVENT TicketStatusUpdate
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 29 MINUTE
DO
CALL Daily_Ticket_Stats();

The 29 minute is because I wanted it to run as close to the hour as I could get it.

This now outputs lovely data in a format that allows me to create a stacked graph in Excel (Spoofed entries from 1st-16th June so I had entries from start of month):

# id, daterun, timerun, totaltickets, verylow, low, med, high, veryhigh
'1', '2016-06-01', '23:00:00', '0', '0', '0', '0', '0', '0'
'2', '2016-06-02', '23:00:00', '0', '0', '0', '0', '0', '0'
'3', '2016-06-03', '23:00:00', '0', '0', '0', '0', '0', '0'
'4', '2016-06-04', '23:00:00', '0', '0', '0', '0', '0', '0'
'5', '2016-06-05', '23:00:00', '0', '0', '0', '0', '0', '0'
'6', '2016-06-06', '23:00:00', '0', '0', '0', '0', '0', '0'
'7', '2016-06-07', '23:00:00', '0', '0', '0', '0', '0', '0'
'8', '2016-06-08', '23:00:00', '0', '0', '0', '0', '0', '0'
'9', '2016-06-09', '23:00:00', '0', '0', '0', '0', '0', '0'
'10', '2016-06-10', '23:00:00', '0', '0', '0', '0', '0', '0'
'11', '2016-06-11', '23:00:00', '0', '0', '0', '0', '0', '0'
'12', '2016-06-12', '23:00:00', '0', '0', '0', '0', '0', '0'
'13', '2016-06-13', '23:00:00', '0', '0', '0', '0', '0', '0'
'14', '2016-06-14', '23:00:00', '0', '0', '0', '0', '0', '0'
'15', '2016-06-15', '23:00:00', '0', '0', '0', '0', '0', '0'
'16', '2016-06-16', '23:00:00', '0', '0', '0', '0', '0', '0'
'17', '2016-06-17', '12:31:22', '4', '1', '0', '0', '0', '3'

This then lets me do a select so I only get the current month imported into Excel:

select * from glpi.glpi_plugin_ns_ticketstats
where month(daterun)=month(NOW())

I'll leave this here so if anyone wants to use it they can, thanks everyone for your time and help :)

iFr4g