Sunday, February 26, 2012

GROUP BY and SUBQUERY for sum

hello everyone
i hope you could help me in this query.

I have one tables: tblA

tblA columns and data are as follows:

Prod Prodchid Req Process

Banana bread ingredientC 1 Y

ingredientC ingredientA 2 Y

ingredientC ingredientB 1 Y

ingredientA white sugar 2 N

ingredientA flour 1 N

ingredientB banana 1 N

ingredientB flour 1 N

I can to query the sum of Req group by Prodchid

SELECT Prodchid,sum(Req) as Qty

FROM tblA

GROUP BY Prodchid

the result is

Prodchid Qty

white sugar 2

flour 2

banana 1

How can I query by multiply require ingredientA.

How can I use SUBQUERY to get anwser below:

Prodchid Qty

white sugar 2x2=4

flour 1X2+1x1=3

banana 1x1=1

Try

SELECT Prodchid,sum(case Prodchid when 'ingredientA' then 2*Req else Req) as Qty

FROM tblA

GROUP BY Prodchid

or

SELECT Prod, Prodchid,sum(case Prod when 'ingredientA' then 2*Req else Req) as Qty

FROM tblA

GROUP BY Prod,Prodchid

|||

Anyway the ideea is to use case...when... in select command|||

"sum(case Prodchid when 'ingredientA' then 2*Req else Req) as Qty"

This only can apply for 'ingredientA' only, how to replace the '2' by subquery result.

|||

pps1 wrote:

How can I query by multiply require ingredientA.

Prodchid Qty

white sugar 2x2=4

flour 1X2+1x1=3

banana 1x1=1

Why do you have to multiply with 2, cause of line

ingredientA white sugar 2 N

or what ?|||

Here it is,

Code Snippet

Create Table #data (

[Prod] Varchar(100) ,

[Prodchid] Varchar(100) ,

[Req] int ,

[Process] char

);

Insert Into #data Values('Banana bread','ingredientC','1','Y');

Insert Into #data Values('ingredientC','ingredientA','2','Y');

Insert Into #data Values('ingredientC','ingredientB','1','Y');

Insert Into #data Values('ingredientA','white sugar','2','N');

Insert Into #data Values('ingredientA','flour','1','N');

Insert Into #data Values('ingredientB','banana','1','N');

Insert Into #data Values('ingredientB','flour','1','N');

Code Snippet

Select

data.[Prodchid],

Sum(data.[Req] * ingredient.[Req]) Qty

From

#data as data

Join

(

Select

[Prodchid],

[Req]

From

#data

Where

[Process] = 'Y'

) as ingredient

On ingredient.[Prodchid] = data.[Prod]

Where

data.[Process] = 'N'

Group By

data.[Prodchid]

|||

Dear Manivannan,

Thanks, That is what I needed.

In this case, I only can multiply one Level of 'Process', But anywhere it is OK.

Dear ggciubuc,


Multiply with 2, because ingredientA require 2.

All material in ingredientA should multiply with 2.

Thanks

No comments:

Post a Comment