HELP with Query

[Pepe Pérez]

I hace 2 tables

CREATE TABLE SALES (
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  SALE_NUMBER NCHAR VARYING(10) COLLATE ES_ES_CI_AI,
  SALE_DATE TIMESTAMP,
  AMOUNT DECIMAL(12, 2))

and

CREATE TABLE PAYMENTS (
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  SALE_ID BIGINT,
  AMOUNT DECIMAL(12, 2))

SALES table has these values:

 1 A01         18/07/2025 12:15    125,30
 2 A02         18/07/2025 13:07      50,40
 3 A03         19/07/2025 10:05      40,50

PAYMENTS table has these values:

ID SALE_ID AMOUNT
 1       2        40,80
 2       1        20,50
 3       1        25,00

I want a query that obtains the day's sales and the payments that have been made from those sales.

Something like this:

SELECT CAST(S.SALE_DATE AS DATE), SUM(S.AMOUNT) AS SALES_OF_THE_DAY,
       COALESCE (SUM (P.AMOUNT), 0) AS DAILY_PAYMENTS
FROM SALES S
LEFT JOIN PAYMENTS P ON (P.SALE_ID=S.ID)
GROUP BY CAST(S.SALE_DATE AS DATE)

This query works fine until I have more than 1 payment for a sale.

With the data I have indicated, I have these results:

18/07/2025           301,00          86,30
19/07/2025            40,50           0,00

The correct result I'm trying to get is this:

18/07/2025           175,70          86,30
19/07/2025            40,50           0,00

Please, Can someone help me? I'm a little dim today and I can't find a way.

Thank you.

[Dimitry Sibiryakov]

Pepe Pérez wrote 19.07.2025 13:58:
> Can someone help me? I'm a little dim today and I can't find a way.

If you need only one column from PAYMENTS, subquery is fine. Otherwise use
Derived Table like this:

SELECT CAST(S.SALE_DATE AS DATE), SUM(S.AMOUNT) AS SALES_OF_THE_DAY,

COALESCE (SUM(P.PAY_SUM), 0) AS DAILY_PAYMENTS

FROM SALES S
LEFT JOIN

(select SALE_ID, SUM(AMOUNT) AS PAY_SUM FROM PAYMENTS GROUP BY P.SALE_ID) P
ON (P.SALE_ID=S.ID)
GROUP BY 1

--
WBR, SD.

[Svein Erling Tysvær]

WITH TMP(ID, SALE_DATE, SOLD, PAID) AS

(SELECT S.ID, CAST(S.SALE_DATE AS DATE), S.AMOUNT, SUM(P.AMOUNT)

FROM SALES S

LEFT JOIN PAYMENTS P ON S.ID = P.SALE_ID

GROUP BY 1, 2, 3)

SELECT SALE_DATE, SUM(SOLD) AS SALES_OF_THE_DAY, SUM(PAID) AS DAILY_PAYMENTS

FROM TMP

GROUP BY 1

is another alternative. The problem with your original query was that you grouped individual sales and payments simultaneously. My suggestion avoids this by grouping payments in the subselect.

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/80fa1805-fc03-4295-a2ab-d65a07d17631%40ibphoenix.com.