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