Select using Join with Distinct

Navigation
0

I have two tables:

tb_movement 

| cod | dt_producao  | qt_prod |
|-----|--------------|---------|
|   1 | '04.08.2016' |      10 |
|   2 | '04.08.2016' |       5 |
|   3 | '09.08.2016' |      12 |
|   4 | '10.08.2016' |       3 |

tb_romaneio 

| cod | dt_romaneio  | qt_entregue |
|-----|--------------|-------------|
|   1 | '09.08.2016' |        2356 |
|   2 | '04.08.2016' |         156 |
|   3 | '04.08.2016' |        4563 |
|   4 | '04.08.2016' |        1253 |

I need to average qt_prod and qt_get getting between two dates, I'm doing this: 

SELECT T1.DT_PRODUCAO, SUM(T1.QT_PROD), SUM(T3.QT_ENTREGUE)
FROM TB_MOVIMENTO T1
INNER JOIN TB_ROMANEIO T3 ON (T3.DT_ROMANEIO = T1.DT_PRODUCAO)
WHERE
(T1.DT_PRODUCAO BETWEEN '04.08.2016' AND '09.08.2016')
GROUP BY
T1.DT_PRODUCAO

But it is not bringing as I want, I need you to bring me like this: 

| dt_producao  | qt_prod | qt_entregue |
|--------------|---------|-------------|
| '04.08.2016' |      15 |         156 |
| '09.08.2016' |      12 |        2356 |
    
asked by anonymous 24.08.2016 / 15:07

1 answer

1

Initially I thought about answering this question using a SubQuery, but seeing that Firebird supports Common Table Expressions (MySql ... I'm looking at you) 

WITH CTE_Movimento AS (
    SELECT dt_producao, SUM(qt_prod) as qt_prod
    FROM tb_movimento 
    GROUP BY dt_producao
), CTE_Romaneio AS (
    SELECT dt_romaneio, SUM(qt_entregue) as qt_entregue
    FROM tb_romaneio 
    GROUP BY dt_romaneio 
)

SELECT dt_producao, qt_prod, qt_entregue 
FROM CTE_Movimento
JOIN CTE_Romaneio ON CTE_Movimento.dt_producao = CTE_Romaneio.dt_romaneio
WHERE dt_producao BETWEEN '04.08.2016' AND '09.08.2016'
    
24.08.2016 / 15:24
Delphi registry key permissions Value drop in getIntent ()