Count 2 different criteria

ASP Free Forums Sponsor:

Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!

May 4th, 2008, 12:42 PM

davepick

Registered User

Join Date: May 2008

Posts: 2

Time spent in forums: 38 m 22 sec
Reputation Power: 0

Query - General - Count 2 different criteria

Hi,
I am looking for someone to point me in the right direction.

I need a query that will count 2 different criteria from a table and then show grouped results. I can group by College and get 1 set of results OK (either 'No of Pupils' OR 'No of Teachers'), but not both with one statement.

Imagine tables:

CollegeID, CollegeName
0 , Kings
1 , London
2 , Manchester
3 , Salford

SubjectID, Name, College, Role, Subject
0 , John, Kings, Pupil, Geography
1 , John, Kings, Pupil, Maths
2 , Nick, Kings, Pupil, English
3 , Peter, Salford, Pupil, Maths
4 , Bill, Kings, Teacher, Maths
5 , Jim, London, Teacher, English

I want a result which looks like this:

College ! No of Pupils ! No of Teachers
Kings ! 3 ! 1
London ! 0 ! 1
Manchester ! 0 ! 0
Salford ! 1 ! 0
++++++++++++++++++++++++++++++++++++++++
Total ! 4 ! 2

Thanks in advance for any help.

Regards, Dave

May 5th, 2008, 07:54 AM

Wolffy

Slaprentice of Wolves

Join Date: Aug 2007

Location: Mossville, IL

Posts: 1,015

Time spent in forums: 1 Week 4 Days 2 h 3 m 50 sec
Reputation Power: 329

Try something like the following query

SQL Code:

Original - SQL Code
SELECT College, SUM(numPupils), SUM(numTeachers) FROM ( SELECT College, COUNT(Role) as numPupils, 0 FROM table2 WHERE Role = 'Pupil' GROUP BY College UNION SELECT College, 0, COUNT(Role) as numTeachers FROM table2 WHERE Role = 'Teacher' GROUP BY College )

SELECT College, SUM(numPupils), SUM(numTeachers)
FROM (
  SELECT College, COUNT(Role) AS numPupils, 0
     FROM table2
    WHERE Role = 'Pupil'
    GROUP BY College
  UNION
  SELECT College, 0, COUNT(Role) AS numTeachers
    FROM table2
  WHERE Role = 'Teacher'
  GROUP BY College
)

Note, I can see no need for first table at all. Your second table has lots and lots of duplicate data as -- why do you have college name in table2 where it should be the CollegeID?

Comments on this post

davepick agrees: Quick relevent and useful - thanks Wollfy

__________________
Wolffy
------------------------
Teaching people to fish.

May 5th, 2008, 04:10 PM

davepick

Registered User

Join Date: May 2008

Posts: 2

Time spent in forums: 38 m 22 sec
Reputation Power: 0

Thanks

Wolffy,
Thats a great help. It opens up a number of options for me.
The 1st table is to ensure all Colleges would be shown even if no data was present in table 2.

I agree with the redundancy in table 2.
If is actually a table I put together as an example of what I am trying to achieve. I never used my real database as the college analogy would be easier to understand than the tables and process I'm trying to work with.

Hope this makes sense and thanks again.

Dave Pick

Quote:

Originally Posted by Wolffy

Try something like the following query

SQL Code:

Original - SQL Code
SELECT College, SUM(numPupils), SUM(numTeachers) FROM ( SELECT College, COUNT(Role) as numPupils, 0 FROM table2 WHERE Role = 'Pupil' GROUP BY College UNION SELECT College, 0, COUNT(Role) as numTeachers FROM table2 WHERE Role = 'Teacher' GROUP BY College )

SELECT College, SUM(numPupils), SUM(numTeachers)
FROM (
  SELECT College, COUNT(Role) AS numPupils, 0
     FROM table2
    WHERE Role = 'Pupil'
    GROUP BY College
  UNION
  SELECT College, 0, COUNT(Role) AS numTeachers
    FROM table2
  WHERE Role = 'Teacher'
  GROUP BY College
)

Note, I can see no need for first table at all. Your second table has lots and lots of duplicate data as -- why do you have college name in table2 where it should be the CollegeID?

Viewing: ASP Free Forums > Database > SQL Development > Query - General - Count 2 different criteria

« Previous Thread | Next Thread »

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

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!