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
