help with nested query using count

Question

Post reply

help with nested query using count

Angela French

Grasshopper

Points: 22
More actions
October 14, 2005 at 2:05 pm

#363928

Can someone please help!!! I've been stuck on trying to write this one report for three days now! The frustration is making me crazy!
I have a query that is pulling results that I need a count on. I think I need to make my query a nested one, but everything I've tried won't work. My results look like this:
email--------------region----------classID
blah----------------1--------------13
blah----------------1--------------13
blah----------------1--------------13
blah----------------2--------------12
blah----------------2--------------12
blah----------------2--------------12
blah----------------2--------------12
It represents people signed up for a class within a certain region. I need to report how many people in each region signed up for a specific class. I want to format it like this:
Region-----------# of signups----------classID
1------------------3-------------------13
2------------------4-------------------12
The problem is that I don't know how to get the count on the amount of rows per region/classID.
My most recent trial has my query looking like this (it returns errors, but you can see what my nested query is which does work):
SELECT count(distinct email)as signups, region, classID
FROM DDD_training_signups2, DDD_training_classes2 WHERE region, email, categoryID in
(
SELECT distinct(email) as email, region, categoryTitle, C2.classID
FROM DDD_training_class_desc2 D2,
DDD_training_regions R,
DDD_training_classes2 C2,
DDD_training_signups2 S,
DDD_training_dates D
WHERE
D.scheduleID = C2.scheduleID
AND C2.scheduleID = S.scheduleID
AND D2.classID = C2.classID
AND C2.regionID = R.regionID
AND D.classDate >= '7/1/2005' AND D.classDate <= '7/31/2005'
GROUP BY email, region, categoryTitle, C2.scheduleID, C2.classID, S.lname,S.fname
)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Friday evening's a bad time...no time to test etc..

see if you can "play around" with this...

select max(region) Region, count(region) '# of signups', classID
from tblRegion
group by classID
order by Region

**ASCII stupid question, get a stupid ANSI !!!**

Angela French Grasshopper Points: 22 More actions · Answer 2

and hey, it's work time here on the west coast of USA ;-(

Angela French Grasshopper Points: 22 More actions · Answer 3

what does max do? I though it pulled the highest value out of the recordset?

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 4

I don't think you need nested queries at all. Please provide a script that creates all of the tables involved and inserts some sample data. I can't tell how to join DDD_training_signups2 and DDD_training_classes2 from your example.

You also might try using the query builder in Enterprise Manager to get you started. That type of tool helped me a lot when I was learning T-SQL.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor