BorlandTalk.com

Terry · Guest

using Delphi 6 and MSsql2000

the following sql returns a total

select clocknum, sum(hours) as total
from emphours
where status = 'P'
group by clocknum

The status field has three different values, and I need the totals for all
three. {P, A, D}. Rather than write three selects, is there a way to get
three totals with one select statement?

Thanks

Vitali Kalinin · Guest

select status, clocknum, sum(hours) as total
from emphours
group by status, clocknum

"Terry" <cooleyt (AT) woh (DOT) rr.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:41092e69 (AT) newsgroups (DOT) borland.com...

Terry · Guest

select status, clocknum, sum(hours) as total
from emphours
group by status, clocknum

can this sql be modified to only return sums greater than a given value?

Daryl · Guest

Something like...

select status, clocknum, sum(hours) as total
from emphours
HAVING SUM(hours) > 8
group by status, clocknum

The HAVING clause ia applied to each of the groups, in this case each
grouping of status, clocknum.

"Terry" <cooleyt (AT) woh (DOT) rr.com> wrote

afm · Guest

Not being an expert at sql, I build queries in MS Access switch to sql view
and copy and past the sql statements. This was the result.

SELECT emphours.ClockNum, emphours.Status, Sum(emphours.Hours) AS Total
FROM emphours
GROUP BY emphours.ClockNum, emphours.Status;

Appears to agree with the other suggestions.

"Daryl" <developeracc (AT) hotmail (DOT) com> wrote

Daryl · Guest

Sorry I shoud have shown the HAVING clause after the GROUP BY clause.

select status, clocknum, sum(hours) as total
from emphours
group by status, clocknum
HAVING SUM(hours) > 8

"Daryl" <developeracc (AT) hotmail (DOT) com> wrote in message