Home » SQL & PL/SQL » SQL & PL/SQL » how to get the two agregate count from two columns through the same query?
how to get the two agregate count from two columns through the same query? [message #239710] Wed, 23 May 2007 03:07 Go to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
Hi,
I have 2 columns in 1 table..
std_code and result
I want the count of students who ever get pass in 1st test, 2nd test and so on..
I can get the test count against students..
But how can i retreive both counts through single query..

Ex:
Stud_count Test Count
========= ==========
11 3
5 6

here .. 11 students are passed in their 3rd test..
5 students are passed in their 6th test..
how is it possible..Plz assist..

Regards,
Fazal...
Re: how to get the two agregate count from two columns through the same query? [message #239715 is a reply to message #239710] Wed, 23 May 2007 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not clear what you want.
Please post a small example (input and output data).

Don't forget your Oracle version.

Regards
Michel
Re: how to get the two agregate count from two columns through the same query? [message #239727 is a reply to message #239715] Wed, 23 May 2007 03:37 Go to previous messageGo to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
select count(tst_cod),sdtcode
from sdt_tst
where tst_cod = '001'
group by sdtcode
order by count(tst_cod)

output :
count(tst_cod) sdtcode
1 101
1 102
1 103
3 104
3 104

This query retrieved the test count for each sdt_cod
i have to retrieve like
count(tst_cod) count(sdtcode)
1 3
3 2
like that
i.e)
1st test - 3 students are appeared
3rd test - 2 " "

Regards,
Fazal...
Re: how to get the two agregate count from two columns through the same query? [message #239728 is a reply to message #239727] Wed, 23 May 2007 03:38 Go to previous messageGo to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
My oracle version is 10 g
Re: how to get the two agregate count from two columns through the same query? [message #239732 is a reply to message #239727] Wed, 23 May 2007 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still don't understand, post
- what's inside the table (just about 10 lines)
- what you want with these lines
- Oracle version must be with 4 decimals.

Regards
Michel
Re: how to get the two agregate count from two columns through the same query? [message #239815 is a reply to message #239732] Wed, 23 May 2007 06:28 Go to previous messageGo to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
i attached the file with sample data...

My oracle version is
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production

Regards,
Faz...
  • Attachment: result.txt
    (Size: 0.48KB, Downloaded 96 times)
Re: how to get the two agregate count from two columns through the same query? [message #239822 is a reply to message #239710] Wed, 23 May 2007 06:46 Go to previous messageGo to next message
vsc021077
Messages: 2
Registered: May 2007
Junior Member
if your requirement is
11 students are passed in their 3rd test..
5 students are passed in their 6th test..


Then you can use group by function.

Select count(student_code), Test_code
from table_name
group by test_code.

-Vijay
Re: how to get the two agregate count from two columns through the same query? [message #239828 is a reply to message #239822] Wed, 23 May 2007 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I understand you want to know how many students succeeded at first attempt, how many at second attempt and so on.
select nbAttempts, count(*)
from ( select std_cod, count(*) nbAttempts
       from my_table
       group by std_cod
     )
group by nbAttempts
order by nbAttempts
/

Regards
Michel
Re: how to get the two agregate count from two columns through the same query? [message #239861 is a reply to message #239828] Wed, 23 May 2007 08:26 Go to previous message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
Thanks a lot,It's working fine...

Rgds,
Faz..
Previous Topic: PROCEDURE HELP
Next Topic: set off
Goto Forum:
  


Current Time: Sat Dec 10 01:18:50 CST 2016

Total time taken to generate the page: 0.08687 seconds