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 -
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;Populate the table using:
INSERT INTO NUMBERS
(id)
VALUES
(NULL)...for as many values as you need.
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' ) xLEFT 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
How to Group by and Concatenate Fields in Redshift
Sql Server Table Locks in Long Query - Solution: Nolock
Sqlserver - How to Find Dependent Tables on My Table
Why Is There a Scan on My Clustered Index
Varchar(Max) Ms SQL Server 2000, Problems
How to Copy Schema and Some Data from SQL Server to Another Instance
Update Multiple Records in Multiple Nested Tables in Oracle
The Identifier That Starts with ...... Is Too Long. Maximum Length Is 128
Postgres on Conflict Do Update on Composite Primary Keys
Sql Sum Multiple Rows into One
Select Data from Multiple Tables
Generate Create Scripts for a List of Indexes
Indexed View Vs Indexes on Table
How to Check If a Directory Exists Using SQL Server
How to Group by One Column and Retrieve a Row with The Minimum Value of Another Column in T/Sql
Confusing Error About Missing Left Parenthesis in SQL Statement