SELECT a query after another SELECT using Dates-CodePudding

i have a table that uses TIMESTAMPS an needs to be filtered by month, the query is:

SELECT * FROM  tx_gas 
WHERE TO_CHAR(date_prod, 'YYYYMM') = '202103';

now, from the results of this query i need to use another query that groups the dates by day and makes a sum of a field, in the same table:

SELECT SUM(liters) AS LITERS,
        TO_CHAR( TRUNC(date_prod), 'DD/MM/YYYY') AS GROUPED_DATE
FROM tx_gas
GROUP   BY   TRUNC(date_prod)
ORDER   BY   TRUNC(date_prod) ASC;

How can i join this two querys so the two separated queries can be made in just one?

CodePudding user response：

How can i join this two querys so the two separated queries can be made in just one, and the second query can make the search from the results of the first query?

The source table for a query is written in the query itself. You cannot cause it to read different source data without making it a different query.

But supposing that you mean you want to write a new query that performs the same kind of aggregation as the second query on the data that would be returned by the first query, that's pretty simple in this case:

SELECT SUM(liters) AS LITERS,
        TO_CHAR( TRUNC(date_prod), 'DD/MM/YYYY') AS GROUPED_DATE
FROM tx_gas
WHERE TO_CHAR(date_prod, 'YYYYMM') = '202103';
GROUP   BY   TRUNC(date_prod)
ORDER   BY   TRUNC(date_prod) ASC;

The filter criterion given by the WHERE clause controls which data are passed through to the aggregation. The two original queries run over the same source table, and only the first has a filter condition, so adding the first's filter condition to the second yields a query that does as you seem to be asking.

CodePudding user response：

SELECT SUM(liters) AS LITERS,
        TO_CHAR(TRUNC(date_prod), 'DD/MM/YYYY') AS GROUPED_DATE
FROM tx_gas
WHERE TO_CHAR(date_prod, 'YYYYMM') = '202103'
GROUP   BY   TRUNC(date_prod)
ORDER   BY   TRUNC(date_prod) ASC;

That should do the trick; you don't need a second query as you want to filter the table down to a set of rows and then you want to output and aggregate those results