Home » SQL & PL/SQL » SQL & PL/SQL » can I incorporate two counts in a single query ? (Oracle 10G, Ubuntu 11.04)
| can I incorporate two counts in a single query ? [message #567316] |
Wed, 26 September 2012 08:33  |
 |
daudiam
Messages: 51
Registered: June 2011
|
Member |
|
|
User table
id | name
----------
2 | Harry
3 | Mary
Course_User table
summer_course_completed | winter_course_completed | user_id | attendance
------------------------------------------------------------------------
y | n | 2 | 20
y | n | 2 | 40
y | y | 2 | 30
n | n | 3 | 20
n | y | 3 | 60
I wish to list each student's name with the number of summer courses he has completed and the number of winter courses he has completed. I am trying this :
select u.name, count(*)
from user u, course_user cu
where u.id=cu.user_id and cu.summer_course_completed = 'y'
group by u.id;
but I can get only the number of summer courses OR the number of winter courses that each student has completed, but never both simultaneously, through a single query. Is there a way to do that ?
Please don't provide an Oracle-specific solution
[Updated on: Wed, 26 September 2012 10:11] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: can I incorporate two counts in a single query ? [message #567322 is a reply to message #567319] |
Wed, 26 September 2012 09:07  |
Manoj.Gupta.91
Messages: 156
Registered: March 2008
Location: Delhi
|
Senior Member |
|
|
Hi,
Although there is no test case given in the problem and desired output is also no specified. I derived below solutiion from what I could understand.
CREATE TABLE MY_USERS
(
USER_ID NUMBER(10) CONSTRAINT PK_MY_USERS PRIMARY KEY,
USER_NAME VARCHAR2(50)
) ;
CREATE TABLE USER_COURSE
(
SUMMER_COURSES_COMPLETED CHAR(1) DEFAULT 'N',
WINTER_COURSES_COMPLETED CHAR(1) DEFAULT 'N',
USER_ID NUMBER(10),
ATTENDANCE NUMBER(10),
CONSTRAINT FK_MY_USER_COURSES FOREIGN KEY(USER_ID)
REFERENCES MY_USERS( USER_ID )
) ;
INSERT INTO MY_USERS VALUES( 1, 'Harry' ) ;
INSERT INTO MY_USERS VALUES( 2, 'Mary' ) ;
INSERT INTO USER_COURSE VALUES( 'Y', 'N', 1 , 20 ) ;
INSERT INTO USER_COURSE VALUES( 'Y', 'N', 1 ,40 ) ;
INSERT INTO USER_COURSE VALUES( 'Y', 'Y', 1 , 30 ) ;
INSERT INTO USER_COURSE VALUES( 'N', 'N', 2 , 20 ) ;
INSERT INTO USER_COURSE VALUES( 'N', 'Y', 2 , 60 ) ;
COMMIT ;
SELECT
U.USER_ID,
U.USER_NAME,
COUNT(
CASE
WHEN UPPER(C.SUMMER_COURSES_COMPLETED) = 'Y' THEN
1
ELSE NULL
END
) SUMMER_COURSES,
COUNT(
CASE
WHEN UPPER(C.WINTER_COURSES_COMPLETED) = 'N' THEN
1
ELSE NULL
END
) WINTER_COURSES
FROM
MY_USERS U,
USER_COURSE C
WHERE U.USER_ID = C.USER_ID
GROUP BY
U.USER_ID,
U.USER_NAME ;
USER_ID USER_NAME SUMMER_COURSES WINTER_COURSES
1 Harry 3 2
2 Mary 0 1
Thanks & Regards
Manoj
[Updated on: Wed, 26 September 2012 09:11] Report message to a moderator |
|
|
|
|
|
|
|
| Re: can I incorporate two counts in a single query ? [message #567326 is a reply to message #567322] |
Wed, 26 September 2012 10:19 |
 |
Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
A better query is:
SQL> with
2 counts as (
3 select user_id,
4 count(decode(SUMMER_COURSES_COMPLETED, 'Y', 1)) summer,
5 count(decode(WINTER_COURSES_COMPLETED, 'Y', 1)) winter
6 from user_course
7 group by user_id
8 )
9 select u.user_name, c.summer, c.winter
10 from my_users u, counts c
11 where c.user_id = u.user_id
12 order by 1
13 /
USER_NAME SUMMER WINTER
-------------------------------------------------- ---------- ----------
Harry 3 1
Mary 0 1
2 rows selected.
Note that I don't know how you get 2 for Harry's winter data with what you posted.
At first sight, it is 1 (for both).
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Tue May 21 15:45:39 CDT 2013
Total time taken to generate the page: 0.65208 seconds
|
] 
Members
Search
Help
Register
Login
Home
