CODEWITHSUNDEEP
mysqlsqljoin
user1489597
user1489597

Reputation: 303

MySQL Join, Select, multiple conditions

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.

Upvotes: 1

Views: 182

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 2

Related Questions