[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Query with subquery in Where clause returning multiple values

donet programmer

3/20/2007 5:01:00 PM

I am trying to write a query which looks something like below:

Product Table:
ProductId int
Type int
Quantity int

Type in the table above can have any value between 1 and 5. There can
be multiple records in table with same ProductId and Type.

To end users Type is classified in Type A, B and C
where
End User Type A = Type 2
End User Type B = Type 1 and Type 3
End User type C = Type 4 and Type 5

I want to write a query to get quantity for each product by type so my
query will return 3 columns: ProductId, Type and Quantity where Type
will be either A, B or C.

The problem I am facing is how do I group this 1 and 3 and 4 and 5
together ... :-(

I would appreciate any ideas, help and suggestions
Thanks
7 Answers

Plamen Ratchev

3/20/2007 5:21:00 PM

0

Something like this should do it:

SELECT ProductId,
(case [Type]
when 1 then 'B'
when 2 then 'A'
when 3 then 'B'
when 4 then 'C'
when 5 then 'C'
end) AS ProductType,
SUM(Quantity) as ProductQty
FROM Products
GROUP BY ProductId,
(case [Type]
when 1 then 'B'
when 2 then 'A'
when 3 then 'B'
when 4 then 'C'
when 5 then 'C'
end)

HTH,

Plamen Ratchev
http://www.SQL...

Roy Harvey

3/20/2007 5:24:00 PM

0

The key to this is your statement:

>To end users Type is classified in Type A, B and C
>where
>End User Type A = Type 2
>End User Type B = Type 1 and Type 3
>End User type C = Type 4 and Type 5

If that is how your end users see the world, then the database should
reflect that. Create a table, it might be called EndUserTypes, with
one column for the type as the users think of them and one for the
type in the Product table. Populate the table with the 5 rows needed.
Then join to that table when you need the end user's view, and group
on the end user type retrieved.

CREATE TABLE EndUserTypes
( UserUserType char(1) NOT NULL,
Type int NOT NULL)

INSERT EndUserTypes VALUES ('A', 2)
INSERT EndUserTypes VALUES ('B', 1)
INSERT EndUserTypes VALUES ('B', 3)
INSERT EndUserTypes VALUES ('C', 4)
INSERT EndUserTypes VALUES ('C', 5)

SELECT P.productID, U.EndUserType, SUM(P.Quantity) as Quantity
FROM Product as P
JOIN EndUserTypes as U
ON O.Type = U.Type
GROUP BY P.productID, U.EndUserType

Roy Harvey
Beacon Falls, CT

On 20 Mar 2007 10:01:17 -0700, "donet programmer" <arpit.00@gmail.com>
wrote:

>I am trying to write a query which looks something like below:
>
>Product Table:
>ProductId int
>Type int
>Quantity int
>
>Type in the table above can have any value between 1 and 5. There can
>be multiple records in table with same ProductId and Type.
>
>To end users Type is classified in Type A, B and C
>where
>End User Type A = Type 2
>End User Type B = Type 1 and Type 3
>End User type C = Type 4 and Type 5
>
>I want to write a query to get quantity for each product by type so my
>query will return 3 columns: ProductId, Type and Quantity where Type
>will be either A, B or C.
>
>The problem I am facing is how do I group this 1 and 3 and 4 and 5
>together ... :-(
>
>I would appreciate any ideas, help and suggestions
>Thanks

Alejandro Mesa

3/20/2007 5:34:00 PM

0

Try using a "case" expression.

select
ProductID,
case when [Type] = 2 then 'A'
else case when in (1, 3) then 'B' else case when [Type] in (4, 5) then
'C' end end end as new_Type,
sum(quantity) as sum_qty
from
dbo.t1
group by
ProductID,
case ... end end end -- put the case expression here also
go

You can use a CTE if you are working with version 2005 or a derived table.

;with cte
as
(
select
ProductID,
case when [Type] = 2 then 'A'
else case when in (1, 3) then 'B' else case when [Type] in (4, 5) then
'C' end en end as new_Type,
quantity
from
dbo.t1
)
select
ProductID,
new_Type,
sum(quantity) as sum_qty
from
cte
group by
ProductID,
new_Type;


AMB


"donet programmer" wrote:

> I am trying to write a query which looks something like below:
>
> Product Table:
> ProductId int
> Type int
> Quantity int
>
> Type in the table above can have any value between 1 and 5. There can
> be multiple records in table with same ProductId and Type.
>
> To end users Type is classified in Type A, B and C
> where
> End User Type A = Type 2
> End User Type B = Type 1 and Type 3
> End User type C = Type 4 and Type 5
>
> I want to write a query to get quantity for each product by type so my
> query will return 3 columns: ProductId, Type and Quantity where Type
> will be either A, B or C.
>
> The problem I am facing is how do I group this 1 and 3 and 4 and 5
> together ... :-(
>
> I would appreciate any ideas, help and suggestions
> Thanks
>
>

donet programmer

3/20/2007 5:56:00 PM

0

Awesome guys!!!! Thanks .... I think the idea here was to do the GROUP
BY by the CASE statement, I was trying to group it by Type which was
not returning expected results.
I do also like the idea to creating new table for mapping Types with
End User Types.
Thanks everybody for your great ideas.. :-)

donet programmer

3/20/2007 6:19:00 PM

0

Hi everybody, I am wondering if its easy enough to display the data in
the following format:

ProductId TypeA TypeB TypeC
1 100 150 200
2 150 50 100


where the values under Type columns represent the sum of quantity.

What do I need to do to display the results like above.

Thanks

Roy Harvey

3/20/2007 6:26:00 PM

0

SELECT ProcuctID,
SUM(CASE WHEN Type = 2 THEN Quantity ELSE 0 END) as TypeA,
SUM(CASE WHEN Type IN(1,3) THEN Quantity ELSE 0 END) as TypeB,
etc.

Roy Harvey
Beacon Falls, CT

On 20 Mar 2007 11:19:10 -0700, "donet programmer" <arpit.00@gmail.com>
wrote:

>Hi everybody, I am wondering if its easy enough to display the data in
>the following format:
>
>ProductId TypeA TypeB TypeC
> 1 100 150 200
> 2 150 50 100
>
>
>where the values under Type columns represent the sum of quantity.
>
>What do I need to do to display the results like above.
>
>Thanks
>

donet programmer

3/20/2007 7:28:00 PM

0

Thanks Roy... thats superb!!!

Thank you soO00ooOoo soo0oo very much
Cheers!!!!!