1
I need to return the lines of a query, even if it has no value. This table has more status and needs to return all status with the respective months and year chosen, but with the amount zeroed.
Follows query:
SELECT count(*) as total_pedidos,
year(p.data) as ano,
month(p.data) as mes,
p.status as status
FROM pedido as p
LEFT JOIN relacao as r ON r.id = p.relacao_id
WHERE
p.data between '2020-01-01 00:00' and '2020-05-01 23:59'
and p.status in ('AGENDADO')
GROUP BY
month(p.data),
YEAR(p.data),
p.status
Following current result:
total_pedidos | ano | mes | status
-------------------------------------------------------------------
36 | 2020 | 4 | AGENDADO
I need you to come back:
-------------------------------------------------------------------
total_pedidos | ano | mes | status
-------------------------------------------------------------------
0 | 2020 | 1 | AGENDADO
0 | 2020 | 2 | AGENDADO
0 | 2020 | 3 | AGENDADO
36 | 2020 | 4 | AGENDADO
I appreciate any help!
Create an auxiliary table of months and make a LEFT OUTER JOIN with your query above using
COALESCE(total_pedidos, 0). If you are using Postgresql then you can use:SELECT * FROM generate_series('2020-01-01 00:00'::timestamp, '2020-05-01 23:59', '1 month');.– anonimo
Friend, I made the following querye and is returning only one line yet :(
– alex
create table #tempMeses (id int Identity(1,1), mes varchar(250),) Insert into #tempMeses select 'January' Insert into #tempMeses select 'February' Insert into #tempMeses select 'March' Insert into #tempMeses select 'April' Insert into #tempMeses select 'May' Insert into #tempMeses select 'June' Insert into #tempMeses select 'July' Insert into #tempMeses select 'August' Insert into #tempMeses select 'September' Insert into #tempMeses select 'October' Insert into #tempMeses select 'November' Insert into #tempMeses select 'December' continue...
– alex
How you put your #tempMeses table on the right side of JOIN then use
RIGHT OUTER JOIN.– anonimo
Friend, keeps bringing only one line.
– alex
Remember that in an OUTER JOIN the fields that have no corresponding in the other table stay with NULL in that row. Try swapping Month(p.data_inspectao) for #tempMeses.id in the SELECT field list.
– anonimo
I don’t know what happens, even after putting #tempMeses.id, keeps appearing only one line. Do I have to change something else or have another scheme?
– alex
https://answall.com/questions/446579/sql-registros-0
– Motta
Guys, it hasn’t worked out yet.
– alex
Post the query that is not displaying the desired result.
– anonimo
Anonymous friend, after your recommendations the query looked like this: SELECT COALESCE(Count(*) , 0) the total_orders, year(p.data) the years, Month(p.data) the months, p.status the status FROM request as p RIGHT OUTER JOIN #tempMeses ON #tempMeses.id = Month(p.data) LEFT JOIN relacao as r ON r.id = p.relacao_id WHERE p.data between '2020-01-01 00:00' and '2020-05-01 23:59' and p.status in ('SCHEDULED') GROUP BY Month(p.data), YEAR(p.data), p.status
– alex
What I suggested was:
SELECT COALESCE(count(*) , 0) as total_pedidos, year(p.data) as ano, #tempMeses.id as mes, p.status as status 
FROM pedido as p RIGHT OUTER JOIN #tempMeses ON #tempMeses.id = month(p.data) 
WHERE p.data between '2020-01-01 00:00' and '2020-05-01 23:59' and p.status in ('AGENDADO') 
GROUP BY #tempMeses.id, YEAR(p.data), p.status. Ending this discussion.– anonimo
Thank you very much to everyone who helped me, hugs!
– alex