How to write an oracle SQL query that involves multiple selects

Question

I have this query that gives me the name of the item, the average price and the count for a specific year as below.

name      Avg_price_2019    count
---------------------------------
X         23.9              234
Y         21.8               59

SQL:

SELECT 
    AVG(Amount) Avg_price_2019, name 
FROM
    (SELECT 
         name, SUM(price_amount) Amount, COUNT(*) 
     FROM 
         myTable
     WHERE
         (To_date("Activity Date", 'mm-dd-yyyy') >= TO_DATE('09/01/2019', 'mm/dd/yyyy'))
         AND (To_date("Activity Date", 'mm-dd-yyyy') <= TO_DATE('09/17/2019','mm/dd/yyyy')) 
     GROUP BY
         name)
GROUP BY
    name;

I want it to return more years as below

name | Avg price 2018 | count | Avg price 2019 | count

For the results of 2018, I need the same query just changing the year.

How can I combine these two selects in the same query to produce the above result?

Why on earth are you storing date values in a VARCHAR column? That is a really bad idea. — , Sep 22 '19 at 20:34
The query you are showing does *not* calculate averages. The subquery (derived table) gives you one row per name with the total amount and the count. The main query groups by name again, so `AVG(Amount)` is just `Amount`, because the average over one value (one row) is the value itself of course. Having said this, your main query is pointless. — Thorsten Kettner, Sep 22 '19 at 21:55
On a sidenote: You can use date literals, e.g. `DATE '2019-09-01'`, so you don't have to convert a string with `TO_DATE`. — Thorsten Kettner, Sep 22 '19 at 21:58

GMB · Accepted Answer · 2019-09-22T20:38:12.623

You seem to want conditional aggregation. You query could most probably be simplified as follows:

SELECT
    name 
    AVG(CASE WHEN EXTRACT(YEAR FROM Activity_Date) = 2019 THEN Amount END) avg_price_2019,
    SUM(CASE WHEN EXTRACT(YEAR FROM Activity_Date) = 2019 THEN 1 ELSE 0 END) count_2019,  
    AVG(CASE WHEN EXTRACT(YEAR FROM Activity_Date) = 2018 THEN Amount END) avg_price_2018,
    SUM(CASE WHEN EXTRACT(YEAR FROM Activity_Date) = 2018 THEN 1 ELSE 0 END) count_2018
FROM myTable 
GROUP BY name

You seem to be storing dates as string, in format mm-dd-yyyy. You can use SUBSTR() to extract the year part, like:

SELECT
    name 
    AVG(CASE WHEN SUBSTR(Activity_Date, -4) = '2019' THEN Amount END) avg_price_2019,
    SUM(CASE WHEN SUBSTR(Activity_Date, -4) = '2019' THEN 1 ELSE 0 END) count_2019,  
    AVG(CASE WHEN SUBSTR(Activity_Date, -4) = '2018' THEN Amount END) avg_price_2018,
    SUM(CASE WHEN SUBSTR(Activity_Date, -4) = '2018' THEN 1 ELSE 0 END) count_2018
FROM myTable 
GROUP BY name

Finally, if you want to filter on a specific period of the year (September 1st to September 17th), then you can add a WHERE clause to the query. Still assuming dates stored as strings, that could be:

WHERE SUBSTR(Activity_Date, 1, 5) BETWEEN '09-01' AND '09-17'

You missed an opportunity to use Oracle's pivot syntax. https://stackoverflow.com/a/23939719/119477 — Conrad Frix, Sep 23 '19 at 04:01

score 0 · Answer 2 · answered Sep 22 '19 at 20:39

In this case I would use a 'fake map column'

select a.NAME, a.AVG. a.CNT, b.AVG, b.CNT from 
(
select '1' as MAP, NAME, AVG, CNT from ... your query [2018 table] ...
) a
left join
(
select '1' as MAP, NAME, AVG, CNT from ... your query [2019 table] ...
) b
on a.MAP = b.MAP

I did not test the code but what I am trying to say is that you can create a dummy column in each table and then left join them on that column to get them side by side.

Also for better performance I would recommend using 'BETWEEN' when filtering Datetime Eg: DATE BETWEEN date1 and date2

score 0 · Answer 3 · edited Sep 22 '19 at 22:26

It is the where clause:

Create Table myTable 
(

    myTableID int,
    amount int,
    price_amount int,
    to_date DateTime,
    activity_date DateTime

)

Insert Into myTable (myTableID, amount, price_amount, activity_date) values (1, 1, 2, '1/1/2019')
Insert Into myTable (myTableID, amount, price_amount, activity_date) values (1, 1, 4, '1/1/2018')

select * from myTable

Select AVG(amount) as Avg_price_2019, 
   (SELECT SUM(price_amount) FROM myTable) as test from myTable
   where activity_date >= Convert(DateTime, '09/01/2019')
   and activity_date <= Convert(DateTime, '09/17/2019')

You seem to have posted your answer to the wrong request. – Thorsten Kettner Sep 22 '19 at 22:00 — Thorsten Kettner, Sep 22 '19 at 22:00

How to write an oracle SQL query that involves multiple selects

3 Answers3