I have a SQL query summing values for each day, returning only the days that have values. But I would also like to present the days that have not been recorded.
Query:
SELECT DATA,
SUM(VALOR)
FROM TABELA1
WHERE CONTA = '176087'
GROUP BY DATA
Output:
02/10/2015 36312
05/10/2015 25382
06/10/2015 3655
One solution is to create an auxiliary structure with the date range for which you want to generate the results.
If you want to use this solution, you have several alternatives (the list is not exhaustive):
I'll leave a solution here using a CTE.
create table tabela1
(
conta nvarchar(05),
[data] datetime,
valor float
);
insert into tabela1(conta, [data], valor)values
('12345', '2016-05-25', 1),
('12345', '2016-05-26', 3),
('12345', '2016-05-26', 1),
('12345', '2016-05-28', 2),
('12345', '2016-05-28', 3);
with Datas as (
select cast('2016-05-25' as date) [data]
union all
select dateadd(dd, 1, t.[data])
from Datas t
where dateadd(dd, 1, t.[data]) <= '2016-05-31'
) -- gera uma view descartável com o intervalo de datas entre 25 a 31 de Maio
select d.[data],
isnull(sum(valor), 0) sum_valor
from Datas d
left join tabela1 t
on t.[data] = d.[data]
and t.conta = '12345'
group by d.[data]
order by d.[data]
The previous statement will generate the following output:
data sum_valor
2016-05-25 1
2016-05-26 4
2016-05-27 0
2016-05-28 5
2016-05-29 0
2016-05-30 0
2016-05-31 0
Stay the SQLFiddle
Would that be?
SELECT DATA, COALESCE(SUM(VALOR),0) FROM TABELA1 WHERE CONTA = '176087' GROUP BY DATA