Creating Sub Group Count Query Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating Sub Group Count Query Problem

Author

Topic

devonkyle
Starting Member

19 Posts

Posted - 2003-07-10 : 18:07:47

I have a query that counts the number of zipcodes that match a user-defined input which gives me the following correct output:
zipcode, city, county, state, Records Found
90044 Hancock Los Angeles CA 1
90044 Los Angeles Los Angeles CA 1
90044 Watts Los Angeles CA 1
90056 Baldwin Hills Los Angeles CA 1
90056 Crenshaw Los Angeles CA 1
90056 Los Angeles Los Angeles CA 1
90056 Windsor Hills Los Angeles CA 1

Is it possible in SQL Server to add an additional subgroup/break total which counts the number of same zipcodes (regardless of the city it is located in) for a result set something like -
zipcode, city, county, state, Records Found
90044 Hancock Los Angeles CA 1
90044 Los Angeles Los Angeles CA 1
90044 Watts Los Angeles CA 1
subtotal for 90044 = 3
90056 Baldwin Hills Los Angeles CA 1
90056 Crenshaw Los Angeles CA 1
90056 Los Angeles Los Angeles CA 1
90056 Windsor Hills Los Angeles CA 1
subtotal for 90056 = 4
GRAND TOTAL COUNT = 7

I also need SQL to give me a GRAND TOTAL/COUNT OF ALL RECORDS Found at the very end. Should I be using some sort of Cube or Rollup for this or the Sum(case) statement? Not sure which approach to take.

My current code reads as follows:

SELECT DISTINCT maincount.zipcode , ziplookup.city,ziplookup.county, ziplookup.statecode, COUNT(*) as "Records Found"

From maincount
join ziplookup
on maincount.zipcode = ziplookup.zipcode

WHERE (maincount.zipcode between 99044 AND 95311) OR
(maincount.zipcode between 90044 AND 95311)
GROUP BY maincount.zipcode, ziplookup.city, ziplookup.county, ziplookup.statecode

Thank you in advance
Devon Kyle
Los Angeles

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-10 : 22:09:36

You can read about ROLLUP in BOL.

Something like (GET RID OF THE DISTINCT. GROUP BY does that.)

SELECT
CASE WHEN GROUPING(m.zipcode) = 1 THEN 'ALL' ELSE m.Zipcode END zipcode ,
CASE WHEN GROUPING(z.city)=1 THEN 'ALL' ELSE z.city END city,
CASE WHEN GROUPING(z.county)=1 THEN 'ALL' ELSE z.county END county,
CASE WHEN GROUPING(z.statecode) = 1 THEN 'ALL' ELSE z.statecode END statecode,
COUNT(*) as "Records Found"

From maincount M
join ziplookup Z
on m.zipcode = z.zipcode

WHERE (m.zipcode between 99044 AND 95311) OR
(m.zipcode between 90044 AND 95311)
GROUP BY m.zipcode, z.city, z.county, z.statecode WITH ROLLUP

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-10 : 22:29:02

How are you displaying this result to the users? on a report? on a web page?

Just let the report calc the SUM() of that column, at the end of the report (usually a report footer section) or do it yourself on the ASP page with a variable and display that value at the bottom of the web page.

it is trivial to do this at the presentation layer, but it can complicate things and require SQL to do more processing than it should need to if you force SQL server to return sub totals or totals like this.

- Jeff

devonkyle
Starting Member

19 Posts

Posted - 2003-07-11 : 14:07:42

I thought it was something like that. Thanks a bunch Sam!!! That should get me on the fast track...

I'm displaying the results on an HTML site. The server side script I'm using is ASP 3.0 and I'm using Dreamweaver MX as my main development tool.

What an awesome site this !!!! gracias!

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-11 : 20:37:18

There are a lot of voices who strongly promote formatting and presentation at the presentation layer. If you have a busy database, they're right.

On the other hand, if your server isn't busy, you can add a little emphasis to your subtotals like this:

SELECT
CASE WHEN GROUPING(m.zipcode) = 1 THEN '<b>Total Zipcodes</b>' ELSE m.Zipcode END zipcode ,
CASE WHEN GROUPING(z.city)=1 THEN 'ALL' ELSE z.city END city,
CASE WHEN GROUPING(z.county)=1 THEN 'ALL' ELSE z.county END county,
CASE WHEN GROUPING(z.statecode) = 1 THEN 'ALL' ELSE z.statecode END statecode,
COUNT(*) as "Records Found"

From maincount M
join ziplookup Z
on m.zipcode = z.zipcode

WHERE (m.zipcode between 99044 AND 95311) OR
(m.zipcode between 90044 AND 95311)
GROUP BY m.zipcode, z.city, z.county, z.statecode WITH ROLLUP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources