Problem with aggregate function with group by

[damo87Locker]

I have a database that includes the following two tables:

 

Artwork: Records artworks that have sold at auction (eg. artist, price)

Auction: Records the auction details (eg. auction date)

 

What I am trying to do is return a set of results showing the average price for paintings sold for a specified artist, grouped by the year that they were sold, for the years 2000 onwards.

 

I can achieve this if I am reporting all paintings. The problems I have though are when I try to have separate columns for paintings of a particular media. For example, If I want to have one column for the realised price for oil paintings, and another column for the same thing for watercolour paintings. The code below is the closest I can get to achieve this, although it seems that if there are no paintings for a particular year for a type of media, it will not show the row. Eg. if there are no watercolour paintings sold in the year 2002 it will not show the row for 2002 even though there may be oil paintings sold in that year:

 

SELECT YEAR( Auction.Date ) AS `Year` , IFNULL( Round( AVG(
Artwork.Price), 0 ) , 0 ), IFNULL( Round( AVG(
Artwork2.Price
), 0 ) , 0 ) 
FROM Auction
LEFT JOIN Artwork ON Auction.AuctionID = Artwork.AuctionID
LEFT JOIN Artwork AS Artwork2 ON Auction.AuctionID = Artwork2.AuctionID
WHERE Artwork.ArtistID =1382
AND Artwork.Media LIKE 'Oil%'
AND Artwork2.ArtistID =1382
AND Artwork2.Media LIKE 'Water%'
AND YEAR( Auction.Date ) >1999
GROUP BY `Year`

 

MySQL version - 5.0.77

 

Any help greatly appreciated!

[Barand]

Try this

 

SELECT w.artistID,
YEAR(a.auction_date) as Year,
AVG(CASE WHEN w.medium LIKE 'Oil%' THEN price ELSE NULL END) as av_oil,
AVG(CASE WHEN w.medium LIKE 'Water%' THEN price ELSE NULL END) as av_water
FROM auction a
LEFT JOIN artwork w USING (auctionID)
GROUP BY artistID, Year

[damo87Locker]

Barand

 

You are a genius. This is what I was looking for. Thank you very much.