+ Post New Thread
Results 1 to 6 of 6
Coding Thread, [SQL] Getting data from my tables in Coding and Web Development; My brain is suffering from the dumb this afternoon, so I could do with some tips. I have a couple ...
  1. #1

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,688
    Thank Post
    516
    Thanked 2,455 Times in 1,899 Posts
    Blog Entries
    24
    Rep Power
    833

    [SQL] Getting data from my tables

    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
    ID int
    First_Name varchar(50)
    Last_Name varchar(50)

    Points
    ID int
    Pupil_ID int
    Category_ID int
    Value int

    Categories
    ID int
    Name varchar(50)

    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?
    Last edited by localzuk; 24th May 2013 at 02:25 PM.

  2. #2

    Join Date
    Feb 2013
    Location
    North Lincolnshire
    Posts
    10
    Thank Post
    5
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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.

  3. #3

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,688
    Thank Post
    516
    Thanked 2,455 Times in 1,899 Posts
    Blog Entries
    24
    Rep Power
    833
    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.

  4. #4


    Join Date
    May 2009
    Posts
    3,033
    Thank Post
    269
    Thanked 802 Times in 607 Posts
    Rep Power
    290
    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 <tables blah>
    where <joins blah>
    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!

  5. #5

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,688
    Thank Post
    516
    Thanked 2,455 Times in 1,899 Posts
    Blog Entries
    24
    Rep Power
    833
    Well, I have finally found the time to come back to this.

    I have gone with a query such as

    Code:
    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.

  6. #6


    Join Date
    May 2009
    Posts
    3,033
    Thank Post
    269
    Thanked 802 Times in 607 Posts
    Rep Power
    290
    Case - yes! Between Oracle, MySql, MsSQL, all the different versions and my ever ageing old brain, I get terribly confused.

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 18
    Last Post: 29th April 2010, 08:32 PM
  2. PHP: Extracting data from a SQL generated array
    By BarryWAaMC in forum Coding
    Replies: 15
    Last Post: 27th March 2010, 07:27 PM
  3. Replies: 0
    Last Post: 25th June 2009, 03:45 PM
  4. Recover data from broken RAID 0 array
    By indie in forum Hardware
    Replies: 23
    Last Post: 8th February 2006, 10:42 AM
  5. Replies: 10
    Last Post: 5th October 2005, 06:01 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •