SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 count for zero result, help me correct this query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chulz90
Starting Member

Indonesia
26 Posts

Posted - 06/19/2013 :  23:43:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/20/2013 :  00:04:50  Show Profile  Reply with Quote
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
Go to Top of Page

chulz90
Starting Member

Indonesia
26 Posts

Posted - 06/20/2013 :  00:11:52  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/20/2013 :  00:17:33  Show Profile  Reply with Quote
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
Go to Top of Page

chulz90
Starting Member

Indonesia
26 Posts

Posted - 06/20/2013 :  00:25:20  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/20/2013 :  00:36:33  Show Profile  Reply with Quote
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
Go to Top of Page

chulz90
Starting Member

Indonesia
26 Posts

Posted - 06/20/2013 :  04:35:31  Show Profile  Reply with Quote
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')

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/20/2013 :  04:50:56  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000