[thelist] SQL: summing and counting the same field, based on value

Phil Turmel philip at turmel.org
Tue Jun 13 19:45:07 CDT 2006


Canfield, Joel wrote:
>>?    You mean that on every row you want it to return the 
>>Count of type=9?
> 
> 
> no, the count of type=9 *where all the rest of the 'where' criteria are
> met as well*
> 
> for instance, I'm looping through multiple call center reps, multiple
> dates, gathering info for all four types. for all four, I want the sum
> of time spent in each state/type. that's done.
> 
> the other bit is, for each agent, for each date, I want to know the
> count of type = 9.
> 
> so is the solution really to create a subquery where I repeat all the
> 'where' criteria? if this really isn't doable elegantly in SQL, I'll
> just do it in the scripting. just hoping there was a simple elegant SQL
> method.

Joel,

Really, what you're asking for is two result sets for each rep/date: a 
four-row cumulative time (all types) and a single-row count (type=9).

If, for efficiency reasons, you only want one query, compute both sum 
and count for each grouping, and ignore the three extra counts in your 
data retrieval loop.  Otherwise, just use two queries.

Case A:

Select rep, date, Sum(start-end), Count(*)
   From calls Group by rep, date, type;

Case B:

Select rep, date, Sum(start-end)
   From calls Group by rep, date, type;
Select rep, date, Count(*)
   From calls Where type=9 Group by rep, date;

HTH,

Phil



More information about the thelist mailing list