asked on # QUERY WITH SUM

If have the following Query for computing sum of hotel guests etc;

SELECT DISTINCTROW QueryFull.AGENCY, Sum(QueryFull.RESVALUE) AS SumResvalue, First(QueryFull.CANCEL) AS [First Of CANCEL]

FROM QueryFull

WHERE (((QueryFull.CANCEL)=False))

GROUP BY QueryFull.AGENCY

ORDER BY Sum(QueryFull.RESVALUE) DESC;

I now would like to change this query to not only include the SUM of RESVALUE but also the sum of RESVALUE but only if PCKGID=1. Is this possible in the same query?

SELECT DISTINCTROW QueryFull.AGENCY, Sum(QueryFull.RESVALUE) AS SumResvalue, First(QueryFull.CANCEL) AS [First Of CANCEL]

FROM QueryFull

WHERE (((QueryFull.CANCEL)=False

GROUP BY QueryFull.AGENCY

ORDER BY Sum(QueryFull.RESVALUE) DESC;

I now would like to change this query to not only include the SUM of RESVALUE but also the sum of RESVALUE but only if PCKGID=1. Is this possible in the same query?

Microsoft Access

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

> "but also the sum of RESVALUE but only if PCKGID=1"

I took this to mean that all the records would still be there as before, just that some of them are summed -- so slightly modifying what Rey gave you if this is the case:

, First(QueryFull.CANCEL) AS [First Of CANCEL]

is irrelevant since false records are knocked out by criteria

if changes need to be made to QueryFull, we will need to see that SQL, thanks

I took this to mean that all the records would still be there as before, just that some of them are summed -- so slightly modifying what Rey gave you if this is the case:

```
SELECT DISTINCTROW QueryFull.AGENCY
, Sum( iif(QueryFull.PCKGID=1,QueryFull.RESVALUE,0) ) AS SumResvalue
FROM QueryFull
WHERE QueryFull.CANCEL=False
GROUP BY QueryFull.AGENCY
ORDER BY Sum( iif(QueryFull.PCKGID=1,QueryFull.RESVALUE,0) ) DESC;
```

and this:, First(QueryFull.CANCEL) AS [First Of CANCEL]

is irrelevant since false records are knocked out by criteria

if changes need to be made to QueryFull, we will need to see that SQL, thanks

Thank you so much!

FROM QueryFull

WHERE QueryFull.CANCEL=False AND QueryFull.PCKGID=1

GROUP BY QueryFull.AGENCY

ORDER BY Sum(QueryFull.RESVALUE) DESC;