asked on

basic query question - one or multiple queries?

This is probably basic, but is stumping me. I am trying to program a dashboard, showing a variety of data points across our database.

For example, I have one table that shows quotes for a job (a job can have multiple quotes); I want to show, for a given job, whether there is an active quote (status field = 1), whether there is a quote that needs internal approval (another field), and whether there is a quote that needs client approval (yet another field).

Can this be done efficiently in a single query? I tried this in a single query using several sum(iif(XXX,1,0)) statements, but I was told this was 'deprecated' (i.e. not the right way to do it). Is there another way? Or do I need to write a whole bunch of very simple queries, and then for the dashboard tie them all together?

The problem, as you can see, if that I am going to a variety of child tables and each query has a slightly different scope/criteria...

Your thoughts are greatly appreciated!

Kevin Cross

I am not sure about the conditional aggregate being deprecated, but if you are looking for another way and each of the fields are flags, i.e., 1 or 0, then you can try like this:

select max(status) as status
     , max(internal_approval) as internal_approval
     , max(client_approval) as client_approval
from your_table;

Open in new window

M-1

terpsichore

ASKER

This is helpful for this scenario. How about a scenario where I am looking at a child table and want to sum records that meeting condition 1, condition 2, etc.? Can this be done using multiple aliases and a single query?

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

terpsichore

ASKER

very helpful