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. — user330315
– user330315, Commented Sep 22, 2019 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
– Thorsten Kettner, Commented Sep 22, 2019 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
– Thorsten Kettner, Commented Sep 22, 2019 at 21:58

GMB · Accepted Answer · 2019-09-22 20:38:12Z

1

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'

edited Sep 22, 2019 at 20:38

answered Sep 22, 2019 at 20:28

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Conrad Frix Over a year ago

You missed an opportunity to use Oracle's pivot syntax. stackoverflow.com/a/23939719/119477

AlexARH · Accepted Answer · 2019-09-22 20:39:58Z

0

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

answered Sep 22, 2019 at 20:39

AlexARH

2121 silver badge6 bronze badges

Comments

Makyen · Accepted Answer · 2019-09-22 22:26:41Z

0

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')

edited Sep 22, 2019 at 22:26

Makyen♦

33.6k12 gold badges94 silver badges128 bronze badges

answered Sep 22, 2019 at 20:48

Jeffrey Nelson

11 bronze badge

1 Comment

Thorsten Kettner Over a year ago

You seem to have posted your answer to the wrong request.

Collectives™ on Stack Overflow

How to write an oracle SQL query that involves multiple selects

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related