SQL Server query

Question

I have this simple (NON PRACTICAL) table

STUDENTID   SERVICE
1           a         
1           b         
1           c         
1           d         
1           e         
2           a         
2           b         
2           c         
2           d          
2           e         
3           a          
3           b         
3           c         
4           a         
4           f         
5           a         
5           d         
6           f         
6           g         
7           a         
7           b         
7           c         
7           d         
7           e         
8           a         
8           b         
8           c         
8           d         
8           e

I want to tabulate certain information for instance.

How many students have signed up for service 'a' and service 'b' alone.
How many students have signed up for service 'a' and service 'c' alone.
How many students have signed up for service 'a' and service 'd' alone. etc.

or

How many students have signed up for service 'a' and one other service other than 'a'.
How many students have signed up for service 'b' and two other services other than 'b'.

The number of services might change in the future but that's okay for now.

this is what i have right now and it's not working.

--want to return number of students who have signed up for a and 1 any other service

select COUNT(STUDENTID), service from table 
group by service where service = 'a' and studentid in 
(select studentid from table group by STUDENTID having COUNT(service) = 2)

Based on the table - How many students have signed up for service 'a' and one other service other than 'a'. or How many students have signed up for service 'b' and two other services other than 'b'. — Marin
– Marin, Commented Aug 22, 2012 at 15:54

John Woo · Accepted Answer · 2012-08-22 15:55:47Z

2

Here are some hints regarding your question.

How many students have signed up for service 'a' and service 'b' alone.

SELECT COUNT(*)
FROM
(
    SELECT studentID
    FROM   tableName
    WHERE  Service IN ('A', 'B')
    GROUP BY StudentID
    HAVING COUNT(studentID) = 2
) a

How many students have signed up for service 'a' and service 'c' alone.

SELECT COUNT(*)
FROM
(
    SELECT studentID
    FROM   tableName
    WHERE  Service IN ('A', 'C')
    GROUP BY StudentID
    HAVING COUNT(studentID) = 2
) a

answered Aug 22, 2012 at 15:55

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

@Marin . . . This is the right approach for answering your questions. Use the HAVING clause to define the characteristics you want in your various groups.

Marin Over a year ago

This one works thanks. What about " How many students have signed up for service 'b' and two other services other than 'b' ". Would that be similar to this ? thanks

Matt · Accepted Answer · 2012-08-22 15:46:20Z

0

You can self join

SELECT COUNT(DISTINCT studentid) n
FROM      table t1
LEFT JOIN table t2 on (t1.studentid = t2.studentid)
WHERE t1.service = 'a' AND t2.service <> 'a'

or if you want to know the students...

SELECT studentid, SUM(IF(service = 'a',1,0)) n_a, SUM(IF(service = 'a',0,1)) n_other
FROM table
GROUP BY studentid
HAVING n_a = 1 AND n_other >= 1

answered Aug 22, 2012 at 15:46

Matt

814 bronze badges

1 Comment

Marin Over a year ago

Hi Matt, Thanks for your input. I get ambiguous column studentid (first line) when I try to run it...

Dan · Accepted Answer · 2012-08-22 15:59:05Z

0

What about:

select COUNT(t.STUDENTID)
from 
(select studentid, service  from table group by STUDENTID having COUNT(service) = 2) as t
WHERE t.service = 'a'

edited Aug 22, 2012 at 15:59

answered Aug 22, 2012 at 15:42

Dan

45.8k20 gold badges98 silver badges170 bronze badges

1 Comment

Marin Over a year ago

Hi Dan, For some reason it throws an error around Where. Execting AS,ID or quoted_ID it says. thanks.

Collectives™ on Stack Overflow

SQL Server query

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related