Grouping by Date, Return Row Even If No Records Found

Grouping by date, return row even if no records found

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -

  1. Create a table that only holds incrementing numbers - easy to do using an auto_increment:

    DROP TABLE IF EXISTS `example`.`numbers`;
    CREATE TABLE `example`.`numbers` (
    `id` int(10) unsigned NOT NULL auto_increment,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  2. Populate the table using:

    INSERT INTO NUMBERS
    (id)
    VALUES
    (NULL)

    ...for as many values as you need.

  3. Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-01-01" and "2010-03-01" with your respective start and end dates (but use the same format, YYYY-MM-DD) -

    SELECT x.*
    FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 DAY)
    FROM numbers n
    WHERE DATE_ADD('2010-01-01', INTERVAL n.id -1 DAY) <= '2010-03-01' ) x
  4. LEFT JOIN onto your table of data based on the datetime portion:

       SELECT DATE(x.dt) AS dt,
    COALESCE(SUM(e.value), 0) AS sum_value
    FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 DAY) AS dt
    FROM numbers n
    WHERE DATE_ADD('2010-01-01', INTERVAL n.id -1 DAY) <= '2010-03-01' ) x
    LEFT JOIN ENTRY e ON DATE(e.datetime) = x.dt
    AND e.entryid = 85
    GROUP BY DATE(x.dt)

Why Numbers, not Dates?

Simple - dates can be generated based on the number, like in the example I provided. It also means using a single table, vs say one per data type.

Forcing row for each group even when no relevant data at table

That works for me using left-join

SELECT a.student_type, count(b.student_type) 
FROM students_types a
LEFT JOIN students b ON a.student_type = b.student_type
AND b.registration_time BETWEEN '2018-1-1' AND '2018-12-31'
WHERE a.student_type in(10, 12)
GROUP BY 1;

Or with 'on-the-fly' left join:

SELECT t.student_type, count(b.student_type) 
FROM (SELECT 10 student_type UNION SELECT 12) t
LEFT JOIN students b ON t.student_type = b.student_type
AND b.registration_time BETWEEN '2018-1-1' AND '2018-12-31'
GROUP BY 1;

How can I return a row for each group even if there were no results?

First, generate all the rows, which you can do with a cross join of the customers and the time periods. Then, bring in the information for the aggregation:

select i.id, t.half_month, t.month, t.year, coalesce(sum(nbr_ord), 0)
from (select distinct id from orders) i cross join
(select distinct half_month, month, year
from orders
where month = 7 and year = 2015
) t left join
orders o
on o.id = i.id and o.half_month = t.half_month and
o.month = t.month and o.year = t.year
group by i.id, t.half_month, t.month, t.year;

Note: you might have other sources for the id and date parts. This pulls them from orders.

MySQL group by date, force to return null value if does not exists

thanks to Drew,

Here is a way to perform in a single query, what explained us in detail Drew.

Thanks a lot for your tip using combination of CROSS JOIN

   SELECT 
inr.date AS 'month',
inr.app_id,
r.title,
AVG(r.rank) as 'ranking'
FROM
(
SELECT
DISTINCT DATE_FORMAT(mh.date, '%Y-%m') date,
r.app_id
FROM
applestore_ranking mh
CROSS JOIN
applestore_ranking r
WHERE
mh.app_id = 100
AND
r.app_id IN (100, 1, 2, 3, 4, 5)
) inr
LEFT JOIN
applestore_ranking r ON r.app_id = inr.app_id
AND inr.date = DATE_FORMAT(r.date, '%Y-%m')
GROUP BY
inr.date,
inr.app_id
ORDER BY
CASE WHEN inr.app_id = 100 THEN 1 ELSE 2 END

Grouping and Returning Zero if no records are found

Have you tried moving that last bit in the WHERE clause into the join criteria?
NULL does not = or <> anything :)

So

T.Ticket_Status <> 'CL'

is probably failing to pull those rows that have NULL.

Add
"and T.Ticket_Status <> 'CL'"
as one more item in your Join criteria, to have

 LEFT JOIN SV_Service_Ticket T ON T.Sub_Problem_Id = Q.Problem_Id AND T.Service_Company_Id = Q.Service_Company_Id and T.Ticket_Status <> 'CL'



So you see where we're coming from, here is the basic case we (and you) want to happen.
Are you sure that subquery is settled down?

if object_id('tempdb..#tempItems') is not null
drop table #tempItems;

create table #tempItems (
id int primary key
, category varchar(100)
, subCategory varchar(100)

)
;

insert into #tempItems (id, category, subCategory)
values (1, 'First', 'subA');
insert into #tempItems (id, category, subCategory)
values (2, 'First', 'subB');
insert into #tempItems (id, category, subCategory)
values (3, 'Second', 'subA');
insert into #tempItems (id, category, subCategory)
values (4, 'NotFound', 'subNotFound');

---------------------------------------------------------

if object_id('tempdb..#tempCounts') is not null
drop table #tempCounts;

create table #tempCounts (
id int primary key
, itemId int
)
;

insert into #tempCounts (id, itemId)
values (1, 1);
insert into #tempCounts (id, itemId)
values (2, 2);
insert into #tempCounts (id, itemId)
values (3, 3);
insert into #tempCounts (id, itemId)
values (4, 3);

----------------------------------------------------------

select
items.category
,items.subcategory
, count(counts.id) as count
from
#tempItems items
left join #tempCounts counts on items.id = counts.itemid

group by items.category, items.subcategory
order by items.category, items.subcategory

Group by day and still show days without rows?

In order to accomplish this, you need to have a table (or derived table) which contains the dates that you can then join from, using a LEFT JOIN.

SQL operates on the concept of mathematical sets, and if you don't have a set of data, there is nothing to SELECT.

If you want more details, please comment accordingly.



Related Topics



Leave a reply



Submit