UNION ALL not combining rows

Question

I have this table called "valores_indices" where it imports stocks values every 1 hour, I need to get the values from each monitored stock at 8am and 6pm, those being respectively my "vlrAberto" and "vlrFechado" selects I don't get why these are not merging, I've done it before, any help would be appreciated since I'm running out of ideas, I've tried to nest it without any luck

SELECT
    i.descricao as cotacao,
    m.quantidade as qtdComprada,
    m.valor as vlrUnitario,
    (m.valor * m.quantidade) as valorCompra,
    v.valor as vlrAberto,
    0 as vlrFechado,
    v.data as data
FROM
    movimento_indices m
    LEFT JOIN indices i ON i.idindice = m.idindice
    LEFT JOIN valores_indices v ON v.idindice = m.idindice
WHERE
    v.hora IN ('08:00')
UNION ALL
SELECT
    i.descricao as cotacao,
    m.quantidade as qtdComprada,
    m.valor as vlrUnitario,
    (m.valor * m.quantidade) as valorCompra,
    0 as vlrAberto,
    v.valor as vlrFechado,
    v.data as data
FROM
    movimento_indices m
    LEFT JOIN indices i ON i.idindice = m.idindice
    LEFT JOIN valores_indices v ON v.idindice = m.idindice
WHERE
    v.hora IN ('18:00')

EDIT

This is what I get

This is the expected result

@user12975442 No they are not, One column is having zero while the other has a number. — Mozart, Apr 24 '20 at 18:19

GMB · Accepted Answer · 2020-04-24T18:42:28.370

0

I think that you want conditional aggregation here:

SELECT
    i.descricao as cotacao,
    m.quantidade as qtdComprada,
    m.valor as vlrUnitario,
    (m.valor * m.quantidade) as valorCompra,
    MAX(CASE WHEN v.hora = '08:00' THEN v.valor END) as vlrAberto,
    MAX(CASE WHEN v.hora = '18:00' THEN v.valor END) as vlrFechado,
    v.data as data
FROM
    movimento_indices m
    LEFT JOIN indices i ON i.idindice = m.idindice
    LEFT JOIN valores_indices v ON v.idindice = m.idindice
WHERE v.hora IN ('08:00', '18:00')
GROUP BY 
    i.descricao,
    m.quantidade,
    m.valor,
    m.quantidade,
    v.data

Another option is a lateral join:

SELECT
    i.descricao as cotacao,
    m.quantidade as qtdComprada,
    m.valor as vlrUnitario,
    (m.valor * m.quantidade) as valorCompra,
    v.vlrAberto,
    v.vlrFechado,
    v.data as data
FROM
    movimento_indices m
    LEFT JOIN indices i ON i.idindice = m.idindice
    LEFT LATERAL JOIN (
        SELECT 
            MAX(v.valor) FILTER(WHERE v.hora = '08:00') as vlrAberto,
            MAX(v.valor) FILTER(WHERE v.hora = '18:00') as vlrFechado,
            v.data
        FROM valores_indices v
        WHERE v.idindice = m.idindice AND v.hora IN ('08:00', '18:00')
        GROUP BY v.data
    ) v ON 1 = 1

edited Apr 24 '20 at 18:42

answered Apr 24 '20 at 17:58

GMB

216,147
25
84
135

These do not run on our servers, idk if my company's postgres version is old or what :( – Nimm Apr 24 '20 at 18:30
@user12975442: are you getting an error? Please share the entire error message (and indicate whether it comes from the first or second query). – GMB Apr 24 '20 at 18:31
The first query the problem is the FILTER, when I remove it, it works but doesn't give the desired result (obv reasons haha) – Nimm Apr 24 '20 at 18:38
And the second one I'm not so sure since the software at work doesn't give me much details about error, it just says "syntax error near select", it's a pretty sh*tty software that they use – Nimm Apr 24 '20 at 18:39
@Nimm: OK I updated the first query to not use `FILTER` (although this has been supported in Postgres for ages). It should work for you. – GMB Apr 24 '20 at 18:44
@Nimm: note that this solution should be much more efficient than `UNION ALL` + aggregation (which requires multiple scans of the table, while my query scans the table only once). – GMB Apr 24 '20 at 18:45

Mozart · Answer 2 · 2020-04-24T18:44:43.553

I'm not much into PostGreSql but in SQL Server this is how you do it, Give it a try.

SELECT cotacao, qtdComprada, vlrUnitario, valorCompra, SUM(vlrAberto) vlrAberto, SUM(vlrFechado) vlrFechado, data FROM (
SELECT
    i.descricao as cotacao,
    m.quantidade as qtdComprada,
    m.valor as vlrUnitario,
    (m.valor * m.quantidade) as valorCompra,
    v.valor as vlrAberto,
    0 as vlrFechado,
    v.data as data
FROM
    movimento_indices m
    LEFT JOIN indices i ON i.idindice = m.idindice
    LEFT JOIN valores_indices v ON v.idindice = m.idindice
WHERE
    v.hora IN ('08:00')
UNION ALL
SELECT
    i.descricao as cotacao,
    m.quantidade as qtdComprada,
    m.valor as vlrUnitario,
    (m.valor * m.quantidade) as valorCompra,
    0 as vlrAberto,
    v.valor as vlrFechado,
    v.data as data
FROM
    movimento_indices m
    LEFT JOIN indices i ON i.idindice = m.idindice
    LEFT JOIN valores_indices v ON v.idindice = m.idindice
WHERE
    v.hora IN ('18:00')) temp GROUP BY cotacao, qtdComprada, vlrUnitario, valorCompra, data;

It worked! A few rows still present 0 as a value but most of them are combined now. I'll have to validate the values now — Nimm, Apr 24 '20 at 18:29
The ones with 0 is when the task failed to get the data from stocks, stonks! Thank you so much, I tried to do what you did I just didn't use that temp before my group by — Nimm, Apr 24 '20 at 18:33

UNION ALL not combining rows

2 Answers2