1

I am really stuck at this this multiple condition queries.

There are 2 sets of sample data:

Data

Here's What I want:

Want

I want to know the students that been honorable mentioned by times, ie n=1, n=2, n=3 ... What is their average amount received in 2012 per price, per month, per different type of rewards, and return NULL if there's no value in a particular Month.

So far I have 

SELECT Type_Of_Reward, Honorable_Mention, MONTH(date)       
FROM Data_2 LEFT JOIN      
SELECT(Honorable_Mention, COUNT(*) FROM Data_2 GROUP BY Honorable_Mention ON Student_ID = Honorable_Mention)        
WHERE YEAR(Data_1.date)=2012 AND...

Any comments/helps would be greatly appreciated.

user1489597
  • 193
  • 1
  • 3
  • 12
  • Databases cannot produce data out of nothing. If you want "empty" months represented, then you'll have to provide a list of the date types you want, e.g. a temporary table that contains the years/months you want, that you can join against. Plus, converting the individual months into columns like that calls for a pivot query, which mysql doesn't support. There's workarounds, but they make the query VERY ugly and VERY difficult to maintain. – Marc B Nov 05 '13 at 19:08
  • @MarcB it is possible to achieve that with DATEFORMAT and GROUP BY. The question is are we talking about generating data for only this year ever? So it is a 'run once and throw away' query? – v010dya Nov 05 '13 at 19:11
  • @Marc B, Thanks for your input. Since I am fairly new to MySQL and I know that it would been a lot easier to do such a thing in R/Pivot Table. However, I was asked to do it in MySQL. :( – user1489597 Nov 05 '13 at 19:13
  • Please read up on [link](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns). You will need to take this concept, and use the MONTH() function within the CASE statement, making a new column for every month – AgRizzo Nov 05 '13 at 19:25
  • @AgRizzo Thank you, the link helps! – user1489597 Nov 05 '13 at 20:58

1 Answers1

2

You can try this one by using join and using case for all months make sure you have set proper datatype for date column if you are using any server side language i recommend you to make this type of data representation by using server side language for now you can try this

SELECT d.Type_Of_Reward, d2.Honorable_Mention,
(CASE WHEN d2.`Honorable_Mention` IS NOT NULL AND MONTH(d.`date`)=1 THEN  d2.`Honorable_Mention`   ELSE 0  END) AS jan,
. //for other months
.
.
.

(CASE WHEN d2.`Honorable_Mention_id` IS NOT NULL AND MONTH(d.`date`)=10 THEN  d2.`Honorable_Mention`   ELSE 0  END) AS `oct`
FROM 
`data_1` d LEFT JOIN `data_2` d2 ON(d.`id`=d2 .`Honorable_Mention`)

MONTH

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118