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 Go to next message
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 #567318 is a reply to message #567316] Wed, 26 September 2012 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Tue, 13 March 2012 12:01
For all SQL questions, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...


And FEEDBACK to people that help you.
You didn't do it.
This is a rude and contemptuous behaviour.
So:
1/ give feedback in your previous topics
2/ provide a test case
3/ follow the guide
4/ get help

Regards
Michel
Re: can I incorporate two counts in a single query ? [message #567319 is a reply to message #567318] Wed, 26 September 2012 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 21945
Registered: January 2009
Senior Member
>Please don't provide an Oracle-specific solution
then you should NOT post in Oracle specific forum

Re: can I incorporate two counts in a single query ? [message #567321 is a reply to message #567319] Wed, 26 September 2012 09:02 Go to previous messageGo to next message
daudiam
Messages: 51
Registered: June 2011
Member
@Michel Cadot - I searched for the 'Thanks' or 'Mark this thread as solved' buttons, but couldn't find it, and I didn't want to bump my question to the top for just a simple 'Thank you' comment. And 'contemptuous' ? Towards people I am asking for help ? Anyways... I'll remember to do that.

For the given test data, output should be :

name | number of summer courses completed | number of winter courses completed
------------------------------------------------------------------------------
Harry|                      3             |               2
Harry|                      0             |               1

Re: can I incorporate two counts in a single query ? [message #567322 is a reply to message #567319] Wed, 26 September 2012 09:07 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 186
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 #567323 is a reply to message #567322] Wed, 26 September 2012 09:32 Go to previous messageGo to next message
daudiam
Messages: 51
Registered: June 2011
Member
Thanks a lot. It worked perfectly
Re: can I incorporate two counts in a single query ? [message #567325 is a reply to message #567323] Wed, 26 September 2012 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
daudiam wrote on Wed, 26 September 2012 16:32
Thanks a lot. It worked perfectly


So you found how to thank people now. Wink

Now find how to post a test case

Regards
Michel
Re: can I incorporate two counts in a single query ? [message #567326 is a reply to message #567322] Wed, 26 September 2012 10:19 Go to previous message
Michel Cadot
Messages: 57611
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

Previous Topic: data with semicolon seperated
Next Topic: Performance issue is sql query (3 Merged)
Goto Forum:
  


Current Time: Sat Apr 19 01:25:46 CDT 2014

Total time taken to generate the page: 0.08906 seconds