sum field twice in same query using different criteria

[aas4mis]

I have a table stating descriptions (desc) and quantities (qq). How would I go about getting the sum for all rugs [desc like "*rug*"] and the sum for all non rugs [desc not like "*rug*"] in the same query/subquery?
Thanks in advance. This has been pushing my buttons for the last hour and a half.

[Stewart Ross]

Hi. You can use two calculated fields using IIFs as shown below:

Select desc, sum(IIF(desc like "*rug*", [qq], 0)) as sum1, sum(IIF(desc not like "*rug*", [qq], 0) from Yourtable Group By Desc ... etc

-Stewart

[Stewart Ross]

Sorry, when I wrote the above reply I included the Desc field in the Select. You should NOT include it in your query, as to do so will stop the two computed fields from grouping on the partial matches which you need - just include the two computed fields on their own.

-Stewart

[aas4mis]

Hi. You can use two calculated fields using IIFs as shown below:

Select desc, sum(IIF(desc like "*rug*", [qq], 0)) as sum1, sum(IIF(desc not like "*rug*", [qq], 0) from Yourtable Group By Desc ... etc

-Stewart

Beautiful. Thanks for the help, just what I was looking for. I was even able to reference both aliases and divide to get the percentage of rugs skipped.

** don't forget the ")" after the second IIF statement.

[NeoPa]

...
** don't forget the ")" after the second IIF statement.

You just can't get the staff nowadays :D

[aas4mis]

You just can't get the staff nowadays :D

Ha! I think they do a fine job here on the access forum, from my experience this is the most active. Oh yeah.. good job with the new [C0DE] fields.. looks good.