Query Help Needed, Please 1

[pwinters]

Does anyone know if I can take the following 5 queries and make them into one? They are all querying the same table for similar criteria.

QUERY #1: TOTAL FOR FRAGMENT 1:
SELECT Clng(Sum(Dashboard.Recycled_Usage)) AS SumOfRecycle,
Clng(Sum(Dashboard.Usage)) AS SumOfUsage,
Clng(Sum(Dashboard.Bit)) AS SumOfBit
FROM Dashboard
WHERE Dashboard.Production_Id > 0
AND DASHBOARD.FRAGMENT_ID = 1;

QUERY #2-4: TOTALS FOR FRAGMENTS 2-4:
Same as Query #1 except Dashboard.Fragment_Id = 2, etc.

QUERY #5: TOTAL FOR ALL FRAGMENTS:
String total=
SELECT Clng(Sum(Dashboard.Recycled_Usage)) AS SumOfRecycle, Clng(Sum(Dashboard.Usage)) AS SumOfUsage,
Clng(Sum(Dashboard.Bit)) AS SumOfBit
FROM Dashboard
WHERE Dashboard.Production_Id > 0;


Basically, I'm trying to gather the totals for each fragment and then the total for all the fragments. I want to eventually display all this information together. Can this be done easier than how I have it (Multiple Queries vs. SubQuery or some other means)?

Thanks in advance!

 

[rac2]

Maybe you could use the IIf() function to create five expressions, one for each Fragment_Id. Each expression would have the value to be summed if the fragment were equal to a particular id value, otherwise the value would be zero.

The overall total would sum the values for all rows.

SELECT
       SUM(IIf(Dashboard.Fragment_Id = 2, 
               Dashboard.Recycled_Usage,
               0)
          ) AS SumOfRecycleTwo,
       SUM(IIf(Dashboard.Fragment_Id = 3, 
               Dashboard.Recycled_Usage,
               0)
          ) AS SumOfRecycleThree,
etc.

 

[pwinters]

Thanks rac2! That worked perfectly!