count for zero result, help me correct this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

count for zero result, help me correct this query

Author

Topic

chulz90
Starting Member

26 Posts

Posted - 2013-06-19 : 23:43:17

hello guys i've some problems in my query.

when i write this query , i've got the right result

select distinct(eq_status)from rspi_equipment
where eq_status in('B','TU','R')

when i write this query, i've got the right reult too

select eq_status, count(to_number(eq_qty))
from rspi_equipment
where it_itemnum='02007003' and loc_locationid =9069 and eq_status in('B','TU','R')
group by eq_status

notes: in location 9069 for item 02007003 there's only two status, which is 'B' and 'TU'. So the QTY for item 02007003 whith 'B' status is 7 and 'TU' status is 3

but when i combine that query , there's an error..

here the query

select (select distinct(eq_status)from rspi_equipment
where eq_status in('B','TU','R')), count(NVL(to_number(eq_qty),0))
from rspi_equipment
where it_itemnum='02007003' and loc_locationid =9069 and eq_status in('B','TU','R')
group by eq_status;

the result that what i want is, if item 02007003 didn't have 'R' status it's count 0 .

can you help me to fix my query???
sorry for bad english

thank's for helping

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-20 : 00:04:50

Why do you want sub queries?
-- it is enough... right?
select eq_status, count(NVL(to_number(eq_qty),0))
from rspi_equipment
where it_itemnum='02007003' and loc_locationid =9069 and eq_status in('B','TU','R')
group by eq_status;

NOTE: I think you are using Oracle/DB2.... This forum is for SQL Server....

--
Chandu

chulz90
Starting Member

26 Posts

Posted - 2013-06-20 : 00:11:52

quote:
Originally posted by bandi

Why do you want sub queries?
-- it is enough... right?
select eq_status, count(NVL(to_number(eq_qty),0))
from rspi_equipment
where it_itemnum='02007003' and loc_locationid =9069 and eq_status in('B','TU','R')
group by eq_status;

NOTE: I think you are using Oracle/DB2.... This forum is for SQL Server....

--
Chandu

yes i'am. i'm using pl/sql.
in that forum there's no respon

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-20 : 00:17:33

quote:
Originally posted by chulz90
yes i'am. i'm using pl/sql.
in that forum there's no respon

ok.. what about the result for above query?

--
Chandu

chulz90
Starting Member

26 Posts

Posted - 2013-06-20 : 00:25:20

quote:
Originally posted by bandi

quote:
Originally posted by chulz90
yes i'am. i'm using pl/sql.
in that forum there's no respon

ok.. what about the result for above query?

--
Chandu

the result is Status B = 7 and TU = 3 , there's no status R , i want to show if there's no status R but it still show with zero result.
but the result only two status B and TU only

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-20 : 00:36:33

select eq_status, count(CASE WHEN it_itemnum='02007003' and loc_locationid =9069 THEN to_number(eq_qty) ELSE 0 END) AS Quantity
from rspi_equipment
where eq_status in('B','TU','R')
group by eq_status;

--
Chandu

chulz90
Starting Member

26 Posts

Posted - 2013-06-20 : 04:35:31

quote:
Originally posted by bandi

select eq_status, count(CASE WHEN it_itemnum='02007003' and loc_locationid =9069 THEN to_number(eq_qty) ELSE 0 END) AS Quantity
from rspi_equipment
where eq_status in('B','TU','R')
group by eq_status;

--
Chandu

thanks bandi for helping, but query above have same result with this query below

select eq_status, count(eq_qty) from rspi_equipment
where eq_status in('B','TU','R')

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-20 : 04:50:56

Do you have atleast one eq_status as 'R' in rspi_equipment table?

I think you want the SUM of quantity, not the COUNT
Can you post us the sample data and output for that data?

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources