SQL Group and Subgroup?

[MrTrue]

Ok, I have a SQL query that I'm using to sum a column and group... This works great however, I also would like to pull a subgroup of that into the Recordset to correspond with the total sum... Is there a way I can do this in the SQL statement?

So for example I have Columns
Apple Green 5
Apple Red 4
Banana Yell 5
Banana Red 3

In my recordset I want to return...
Fruit SumRed TotalSum
Apple 4 9
Banana 3 8

Basically it's a sum/Group and a sum sub-group...

In my example

1)I want to sum by grouping First/Last Name to get the total sum of Actvty_Dur,
2)but I also want to sum into each of those groupings if a KOA_Reason.Reason matches a specific criteria... Say "TRAINING" for example...

If I take out the parts pertaining to KOA_Reason I achieve objective 1, how to I acheive object 2 also?

Here is my SQL statement so far...

RSQL = "SELECT KOA_Examiner.CE_First, KOA_Examiner.CE_Last, KOA_Reason.Reason, SUM(KOA_Activity.Actvty_Dur) AS SumOfActvty "
RSQL = RSQL & "FROM KOA_Reason INNER JOIN (KOA_Supervisor INNER JOIN (KOA_Examiner INNER JOIN KOA_Activity ON KOA_Examiner.CE_ID = KOA_Activity.CE_ID) ON KOA_Supervisor.Supervisor_ID = KOA_Examiner.Supervisor_ID) ON KOA_Reason.Reason_ID = KOA_Activity.Reason_ID "
RSQL = RSQL & "WHERE (((KOA_Activity.Activity_Date) Between  '" & weekStart & "' AND '" & weekStart + 6 & "') AND ((KOA_Activity.Is_Valid)=1) AND ((KOA_Supervisor.SUP_Alias)='" & ID_ALS & "')) "
RSQL = RSQL & "GROUP BY KOA_Examiner.CE_First, KOA_Examiner.CE_Last, KOA_Reason.Reason;"

 

[towerbase]

No doubt Rudy will come up with a more elegant solution but I would tackle it with something like this:

SELECT fruit
     , SUM(SumRed) AS SumRed
     , SUM(TotalSum) AS TotalSum
  FROM
     ( SELECT fruit
            , 0 AS SumRed
            , qty AS TotalSum
         FROM table
       UNION
       SELECT fruit
            , red AS SumRed
            , 0 AS TotalSum
         FROM table
     ) AS Temp
 GROUP BY fruit

Andrew
Hampshire, UK

 

[MrTrue]

Thanks for providing some direction! I've tried to adjust my SQL using your suggestion, but I'm getting an "improper syntax near select" edit... Any thoughts?

SELECT KOA_Examiner.CE_First, KOA_Examiner.CE_Last, KOA_Reason.Reason, SUM(SumofTotal) AS SumOfTotal, SUM(SumofTrain) as SumOfTrain
FROM( 
SELECT KOA_Examiner.CE_First, KOA_Examiner.CE_Last,SUM(KOA_Activity.Actvty_Dur) AS SumofTotal, 0 AS SumofTrain "
FROM KOA_Reason INNER JOIN (KOA_Supervisor INNER JOIN (KOA_Examiner INNER JOIN KOA_Activity ON KOA_Examiner.CE_ID = KOA_Activity.CE_ID) ON KOA_Supervisor.Supervisor_ID = KOA_Examiner.Supervisor_ID) ON KOA_Reason.Reason_ID = KOA_Activity.Reason_ID "
WHERE (((KOA_Activity.Activity_Date) Between  '" & weekStart & "' AND '" & weekStart + 6 & "') AND ((KOA_Activity.Is_Valid)=1) AND ((KOA_Supervisor.SUP_Alias)='" & ID_ALS & "')) "
GROUP BY KOA_Examiner.CE_First, KOA_Examiner.CE_Last;"
UNION
SELECT KOA_Examiner.CE_First, KOA_Examiner.CE_Last, 0 AS SumOfTotal, SUM(KOA_Activity.Actvty_Dur) AS SumOfTrain "
FROM KOA_Reason INNER JOIN (KOA_Supervisor INNER JOIN (KOA_Examiner INNER JOIN KOA_Activity ON KOA_Examiner.CE_ID = KOA_Activity.CE_ID) ON KOA_Supervisor.Supervisor_ID = KOA_Examiner.Supervisor_ID) ON KOA_Reason.Reason_ID = KOA_Activity.Reason_ID "
WHERE (((KOA_Activity.Activity_Date) Between  '" & weekStart & "' AND '" & weekStart + 6 & "') AND ((KOA_Reason.Reason_ID)=1) AND ((KOA_Activity.Is_Valid)=1) AND ((KOA_Supervisor.SUP_Alias)='" & ID_ALS & "')) "
GROUP BY KOA_Examiner.CE_First, KOA_Examiner.CE_Last;"
) AS TEMP
GROUP BY KOA_Examiner.CE_First, KOA_Examiner.CE_Last;"

 

[MrTrue]

Ok my last error was apparently a spacing issue in converting to a string... Now I'm getting an edit that says "The multi part identifier "KOA_Examiner.CE_First" Could not be bound"... Am I missing something?

 

[towerbase]

You need to read Rudy's book Simply SQL. I found it brilliant at explaining what's going on in SQL.

What we are doing with this query is creating a temporary table with the two selects which are concatenated using the UNION. You will see that I have called this temporary table TEMP.

Then the outer bit of the query SELECTs records from this TEMP table. So you should refer to the columns in the TEMP table with the TEMP name and not KOA-Examiner.

So something like:

SELECT TEMP.CE_First, TEMP.CE_Last ....

should work.

Andrew
Hampshire, UK

 

[MrTrue]

I was starting down the right path... I replaced the table name with TEMP in the Select statement after I posted, but completely overlooked the Group By table names... That worked fabulously! As for the book, I'm adding it to my list for next purchases. I've found those Sitepoint publishings to be very helpful in the past, so I'm looking forward to getting more! Thanks again for your guidance!