[RESOLVED] i need a proper/right query

[jp26198926]

hello guys..

can anyone tell me what is the proper query of display item that i want..
i want to display the # of available,sold,used,defective items in every shipment.


Shipment | Available | Sold | Used | Defective
---------+-----------+------+------+----------+


i tried this query but not displaying the expected result..

rs.Open "Select DISTINCT [shipment],(Select count([status]) from item where [status]='available') as Available," & _
                          "(Select count([status]) from item where [status]='sold') as Sold," & _
                          "(Select count([status]) from item where [status]='used') as Used," & _
                          "(Select count([status]) from item where [status]='defective') as Defective" & _
                          " from item", cn, adOpenDynamic, adLockOptimistic

thnk you...

note: all fields are in the same table..

[FunkyDexter]

For this sort of thing there's a neat trick using the sum, case and group by statements. Something like:-

Select Shipment,
         Sum(Case When Status = 'available' then 1 else 0 end) as Available,
         Sum(Case When Status = 'sold' then 1 else 0 end) as Sold,
         Sum(Case When Status = 'used' then 1 else 0 end) as Used,
         Sum(Case When Status = 'defective' then 1 else 0 end) as Defective,
From Item
Group By Shipment

[TysonLPrice]

SQL 2005

If object_id('tempdb..##Item') is not null drop table ##Item

create table ##Item(Status VarChar(10))
insert into ##Item(Status) Values('available')
insert into ##Item(Status) Values('available')
insert into ##Item(Status) Values('available')
insert into ##Item(Status) Values('Sold')
insert into ##Item(Status) Values('Sold')
insert into ##Item(Status) Values('used')
insert into ##Item(Status) Values('used')
insert into ##Item(Status) Values('used')
insert into ##Item(Status) Values('used')
insert into ##Item(Status) Values('used')
insert into ##Item(Status) Values('defective')
insert into ##Item(Status) Values('defective')
insert into ##Item(Status) Values('defective')
insert into ##Item(Status) Values('defective')
insert into ##Item(Status) Values('defective')
insert into ##Item(Status) Values('defective')
insert into ##Item(Status) Values('defective')

SELECT * FROM ##Item
 PIVOT (COUNT(Status) FOR status IN ([available],[defective],[Sold],[used])) p

drop table ##Item

[jp26198926]

@FunkyDexter and TysonLPrice: thank you for your reply, i will try your suggestion sir..

btw i am using ms access and adodb

thanks..

[techgnome]

@Tyson - Interesting solution to the problem. I don't have nearly enough PIVOT experience I should have... it's not something I think of out of the box. Very cool though.

@jp261... - sigh... why do people wait until the discussion has started to mention things like the type of database? I mean, you wouldn't take your Honda to a BMW shop, as for a tune up and then as they are pulling it into the garage "Oh, by the way, it's a Honda..." No... that's part of the initial discussion.

In this case I don't know that Access supports the CASE statement, but it should support the PIVOT... so that's going to be your best bet.

-tg

[nubie]

since you're using access, try this query

Transform count(Status) as StatusCount
SELECT Shipment
FROM Item
group by Shipment
pivot status

[jp26198926]

@jp261... - sigh... why do people wait until the discussion has started to mention things like the type of database? I mean, you wouldn't take your Honda to a BMW shop, as for a tune up and then as they are pulling it into the garage "Oh, by the way, it's a Honda..." No... that's part of the initial discussion.

In this case I don't know that Access supports the CASE statement, but it should support the PIVOT... so that's going to be your best bet.

-tg

heheeh i sorry sir.. i forgot to mentioned that yesterday.. i will be careful next time.. anyway thanks for your reply,..

@nubie: Thank you sir.. your solution solves my thread..

[techgnome]

heheeh i sorry sir.. i forgot to mentioned that yesterday.. i will be careful next time.. anyway thanks for your reply,..

@nubie: Thank you sir.. your solution solves my thread..

Sorry... I jsut see it so much... you simply got the brunt of it (and it was even watered down a bit) ... at least it only made it three posts... I've seen threads where we're 12 posts in and the OP will go "I'm using Access does that matter?".... somedays it's enough to make you bang your head in to the desk.

-tg