Select using Join with Distinct

Asked

Viewed 2,205 times

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_romance

| 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 the qt_prod and qt_delivered picking between two dates, I’m doing so:

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 you’re not bringing it like I want it, I need you to bring it like this:

| dt_producao  | qt_prod | qt_entregue |
|--------------|---------|-------------|
| '04.08.2016' |      15 |         156 |
| '09.08.2016' |      12 |        2356 |
sql firebird join inner

  • What are you bringing to you?

  • before group by has no and, and what has the title Distinct ?

1 answer

1


Initially I thought to answer this question using a Subquery, but seeing that Firebird supports Common Table Expressions (Mysql... I’m looking for 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'

  • Thank you very much.. I did with Subquery tbm worked out..

Browser other questions tagged sql firebird join inner

You are not signed in. Login or sign up in order to post.