[SQL] Getting data from my tables

[localzuk]

My brain is suffering from the dumb this afternoon, so I could do with some tips.

 

I have a couple of tables (simplified below) in MSSQL:

 

Pupils

[TABLE=class: grid, width: 500]

[TR]

[TD]ID[/TD]

[TD]int[/TD]

[/TR]

[TR]

[TD]First_Name[/TD]

[TD]varchar(50)[/TD]

[/TR]

[TR]

[TD]Last_Name[/TD]

[TD]varchar(50)[/TD]

[/TR]

[/TABLE]

 

Points

[TABLE=class: grid, width: 500]

[TR]

[TD]ID[/TD]

[TD]int[/TD]

[/TR]

[TR]

[TD]Pupil_ID[/TD]

[TD]int[/TD]

[/TR]

[TR]

[TD]Category_ID[/TD]

[TD]int[/TD]

[/TR]

[TR]

[TD]Value[/TD]

[TD]int[/TD]

[/TR]

[/TABLE]

 

Categories

[TABLE=class: grid, width: 500]

[TR]

[TD]ID[/TD]

[TD]int[/TD]

[/TR]

[TR]

[TD]Name[/TD]

[TD]varchar(50)[/TD]

[/TR]

[/TABLE]

 

Now, in the points table, there are hundreds of thousands of rows - each pupil can have multiple entries for each field. ie. every category_ID, pupil_ID and value can exist multiple times - they are house points, issued during their various lessons.

 

Obviously, the tables above relate to each other according to the IDs.

 

Now, I want to pull out data as such:

 

Pupil Last Name, Pupil First Name and a total for each category with the field named by its category name. They'd be grouped by pupil.

 

Any ideas how I can do this, my brain has shouted 'temporary tables' at me, but that seems over complex?

Edited May 24, 2013 by localzuk

[rs513]

My SQL is a little rusty but I think you may need something along these lines:

 

SELECT Pu.Last_name, Pu.First_Name, C.Name, sum(Po.value) as Cat_Total

FROM pupils AS Pu JOIN points AS Po ON Pu.ID = Po.Pupil_ID JOIN Categories as C ON Po.Category_ID = C.ID

GROUP BY Pu.Last_name, Pu.First_Name, C.Name

 

Have to double check the terminology but hope it gets you on the right path.

[localzuk]

That's not quite what I want -that'd give me multiple rows per person still.

 

From what I can figure out, I'm going to need to use pivot tables.

[pcstru]

You can use something like :

 

select pu.first_name, pu.first_surname,

sum(iif(c.name="Category 1",pt.value,0)) "Category 1",

sum(iif(c.name="Category 2",pt.value,0)) "Category 2"

from

where

group by pu.first_name, pu.first_surname

 

 

The if inside the sum will ensure only appropriate records get summed into the total. This is old style pivot!

[localzuk]

Well, I have finally found the time to come back to this.

 

I have gone with a query such as

 

SELECT Pupil.Last_Name + ' ' + Pupil.First_Name AS Name, Pupil.Last_Name, Pupil.First_Name,                          SUM(CASE Point_Category.ShortName WHEN 'HW-' THEN Point_Entry.Value ELSE 0 END) AS [HW-], 
                        SUM(CASE Point_Category.ShortName WHEN 'LoE' THEN Point_Entry.Value ELSE 0 END) AS LoE, 
                        SUM(CASE Point_Category.ShortName WHEN 'Present' THEN Point_Entry.Value ELSE 0 END) AS Present
FROM            Pupil 
INNER JOIN Point_Entry ON Point_Entry.Pupil_ID = Pupil.ID 
INNER JOIN Point_Category ON Point_Category.ID = Point_Entry.Point_Category_ID
INNER JOIN Reg_Group ON Reg_Group.ID = Pupil.Reg_Group
WHERE (Reg_Group.Name LIKE '08HS')
GROUP BY Pupil.First_Name, Pupil.Last_Name
ORDER BY Pupil.Last_Name, Pupil.First_Name

 

IIF() isn't in SQL 2008 R2 - it was only added in 2012.

 

But the above works for me.

 

The category names are filled out using PHP - ie. I grab all the column names in one query, then create the above using those categories.

[pcstru]

Case - yes! Between Oracle, MySql, MsSQL, all the different versions and my ever ageing old brain, I get terribly confused.