OraFAQ Forum: SQL & PL/SQL » SQL query

Home » SQL & PL/SQL » SQL & PL/SQL » SQL query - count

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL query - count [message #1924]

Tue, 11 June 2002 09:41

Tim
Messages: 49
Registered: October 2000

Member

I am creating the following sql query,and it does not work as the select count arguements fail, does anyone know the solution to this. Thanks
select
obsc.name BSC,
obts.name BTS,
otrx.object_instance TRX,
count(channel.channel_type where channel_type =2) TCHD#,
count(channel.channel_type where channel_type =1) TCHD#
from
objects obsc,
c_bsc bsc,
objects obcf,
c_bcf bcf,
objects obts,
c_bts bts,
objects otrx,
c_trx trx,
objects ochannel,
c_channel channel
where
obsc.object_instance !=0 and
obsc.int_id=bsc.int_id and
obsc.int_id=obcf.parent_int_id and
obcf.int_id=bcf.int_id and
obcf.int_id=obts.parent_int_id and
obts.int_id=bts.int_id and
otrx.int_id=trx.int_id and
otrx.parent_int_id=obts.int_id and
ochannel.int_id=channel.int_id and
ochannel.parent_int_id=otrx.int_id and
bsc.conf_name like '<ACTUAL>' and
bcf.conf_name like '<ACTUAL>' and
bts.conf_name like '<ACTUAL>' and
trx.conf_name like '<ACTUAL>' and
channel.conf_name like '<ACTUAL>' and
channel.channel_type = 2
group by
obsc.name,
obts.name,
otrx.object_instance;
exit;

Report message to a moderator

Re: SQL query - count [message #1926 is a reply to message #1924]

Tue, 11 June 2002 11:04

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

you cannot give a where condition inside the count.
you can make use of group by clause.
the following query gives the count of employees,in each department.

SQL> select deptno, count(empno) from emp group by deptno;

    DEPTNO COUNT(EMPNO)
---------- ------------
        10            3
        20            5
        30            6

SQL>

Report message to a moderator

Re: SQL query - count [message #1934 is a reply to message #1924]

Wed, 12 June 2002 01:35

Tim
Messages: 49
Registered: October 2000

Member

Hi,

Thanks very much for your prompt answer, it works. Is there any way I can have counts displayed on the same line for example:

Dept_10 Dept_20 Dept_30 (This line headings)
3 5 6 (This line counts)

regards

Report message to a moderator

Re: SQL query - count [message #2620 is a reply to message #1924]

Wed, 31 July 2002 08:45

Harry
Messages: 17
Registered: March 2001

Junior Member

Hi Tim,

Can you plz tell me what solution did you find for you posted problem. I am facing same prob. I am not able to put count() on two columns of same table..
somthing similar to what you were trying.

Plz reply soon.

Report message to a moderator

Previous Topic:	singly sql query Please reply immediately
Next Topic:	View

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 07:15:01 CDT 2024