SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2002
    Location
    USA
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question SUMs with different WHEREs in one recordset?

    I need to sum points from a record set based on the type of event they were earned at..so the data will contain events of type "CT" or "HT". I want to sum the points earned based on these types..I can get separate SQL statements to work. BUT how can I then create one recordset from these two?

    Note: I have MySQL3.x so UNION is not an option.

    PHP Code:
    mysql_select_db($database_mycdcta$mycdcta);
    $query_rs_eventdata_CT "SELECT eventing_data.event_division, eventing_data.event_rider, eventing_data.event_horse, eventing_data.event_level, Sum(eventing_data.points_total) AS SumOfCT FROM eventing_data WHERE eventing_data.event_type='CT' GROUP BY eventing_data.event_division, eventing_data.event_level, eventing_data.event_horse, eventing_data.event_rider ORDER BY eventing_data.event_level='A' DESC, eventing_data.event_level='I' DESC, eventing_data.event_level='P' DESC, eventing_data.event_level='T' DESC, eventing_data.event_level='N' DESC, eventing_data.event_level='B' DESC, eventing_data.event_division DESC, SumOfCT DESC";
    $rs_eventdata_CT mysql_query($query_rs_eventdata_CT$mycdcta) or die(mysql_error());
    $row_rs_eventdata_CT mysql_fetch_assoc($rs_eventdata_CT);
    $totalRows_rs_eventdata_CT mysql_num_rows($rs_eventdata_CT);

    mysql_select_db($database_mycdcta$mycdcta);
    $query_rs_eventdata_HT "SELECT eventing_data.event_division, eventing_data.event_rider, eventing_data.event_horse, eventing_data.event_level, Sum(eventing_data.points_total) AS SumOfHT FROM eventing_data WHERE eventing_data.event_type='HT' GROUP BY eventing_data.event_division, eventing_data.event_level, eventing_data.event_horse, eventing_data.event_rider ORDER BY eventing_data.event_level='A' DESC, eventing_data.event_level='I' DESC, eventing_data.event_level='P' DESC, eventing_data.event_level='T' DESC, eventing_data.event_level='N' DESC, eventing_data.event_level='B' DESC, eventing_data.event_division DESC, SumOfHT DESC";
    $rs_eventdata_HT mysql_query($query_rs_eventdata_HT$mycdcta) or die(mysql_error());
    $row_rs_eventdata_HT mysql_fetch_assoc($rs_eventdata_HT);
    $totalRows_rs_eventdata_HT mysql_num_rows($rs_eventdata_HT); 

    real world application: http://www.cdcta.com/results/epoints.php
    I want to post the total CT & HT points versus summing them all together.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    try this:
    Code:
    SELECT event_division
         , event_rider
         , event_horse
         , event_level
         , Sum(case when event_type='CT' 
                    then points_total else 0 end ) AS SumOfCT 
         , Sum(case when event_type='HT' 
                    then points_total else 0 end ) AS SumOfHT 
         , Sum(points_total) AS SumOfBoth 
      FROM eventing_data 
    GROUP
        BY event_division
         , event_level
         , event_horse
         , event_rider 
    ORDER 
        BY event_level
         , event_division
         , SumOfBoth desc
    if the levels have a peculiar sort order, you will have to store another column for that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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