CODEWITHSUNDEEP
sql
user4761161
user4761161

Reputation:

SQL aggregate function average

I am a beginner to SQL, trying to find the difference between average price of a cd made by "Skillet" artist with the average price of all cds in a database

E.g. if average of skillet cds = £6, and average price of all cds = £8 then there is a £2 gap (absolute value)

so something like this:

SELECT AVG(cdPrice) FROM cd

subtract 

SELECT AVG(cdPrice)
FROM cd, artist
WHERE artName = "Skillet" AND artist.artID = cd.artID

Thanks

Upvotes: 0

Views: 95

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

Assuming that there is only one artist per CD, just use conditional aggregation:

SELECT AVG(cdPrice) - AVG(CASE WHEN artName = 'Skillet' THEN cdPrice END)
FROM cd LEFT JOIN
     artist
     ON artist.artID = cd.artID;

Two notes:

  • Always use single quotes for string and date constants, in preference to double quotes (single quotes are the SQL standard).
  • Never use commas in the FROM clause. Always use explicit JOIN/ON syntax.

Upvotes: 1

pala_
pala_

Reputation: 9010

Try this, no need for subqueries or cross joins or anything else.

select sum(case when a.name = 'skillet' then c.cdprice else 0 end) / sum(case when a.name = 'skillet' then 1 else 0 end) skillet_average, 
  avg(c.cdprice) cd_avg, 
  abs((sum(case when a.name = 'skillet' then c.cdprice else 0 end) / sum(case when a.name = 'skillet' then 1 else 0 end)) - avg(c.cdprice)) diff 
  from cds c
    inner join artist a
    on c.artist_id = a.id;

demo fiddle: http://sqlfiddle.com/#!9/e29a0/3

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

select x.avg_overall - y.avg_skillet as net_avg
  from (select avg(cdprice) as avg_overall from cd) x
 cross join (select avg(cdprice) as avg_skillet
               from cd
               join artist
              using (artid)
              where artname = 'Skillet') y

Upvotes: 1

SteveK
SteveK

Reputation: 995

If I'm understanding your question correctly, you're looking for something like: SELECT AVG(cdPrice)-(SELECT AVG(cdPrice) FROM cd JOIN artist ON artist.artID = cd.artID WHERE artName = "Skillet") FROM cd;

Upvotes: 3

Related Questions