Compare Aggregates

Dev Shed Forums Sponsor:

December 9th, 2003, 12:11 PM

spar74

Junior Member

Join Date: Nov 2003

Location: Vancouver, WA

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

Compare Aggregates

I have two SQL queries which work one provides the avg number of sales by department and one which shows the total sales by dept. I am trying to combine the queries to show the deptartments that sold less than the average:

SELECT deptname, SUM(saleqty)
FROM qsale
WHERE saleqty < (SELECT SUM(saleqty) / COUNT (DISTINCT deptname)
FROM qsale)
GROUP BY deptname

I'm trying to understand how to do this comparison. The saleqty in my WHERE clause does not reflect the SUM(saleqty). How should this be formatted?

December 9th, 2003, 12:57 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 12 m 47 sec
Reputation Power: 4140

untested:

Code:

select deptname, sum(saleqty)
  from qsale
group 
    by deptname
having sum(saleqty)
     < ( select sum(saleqty) 
              / count(distinct deptname)
           from qsale )

rudy
http://r937.com/

December 9th, 2003, 01:07 PM

spar74

Junior Member

Join Date: Nov 2003

Location: Vancouver, WA

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks Rudy, it worked fine. I did not think to place the sub select in the having clause...

Viewing: Dev Shed Forums > Databases > MS SQL Development > Compare Aggregates

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump