Hi,
I want to get sum of quantity for item presents in specified Purchase_indentno, i dont know how to get this one....
sI got result as..2,5,5 but i want 2,5....because only two item present purchase_indentno=2..Please give a solutions
Technology Tips and News
Hi,
I want to get sum of quantity for item presents in specified Purchase_indentno, i dont know how to get this one....
sI got result as..2,5,5 but i want 2,5....because only two item present purchase_indentno=2..Please give a solutions
you are grouping by itemid
try grouping by purchase_indentno
group by purchase_indentno
Also the query and the result in the screenshot don't match. Are you sure you have posted the correct query
It is always better to post the actual query that the scre
its come sum all the item quantity...result as 12.. but i want 2,5....because only two item present purchase_indentno=2..
Thanks,
in that i combine two table
first one is purchase table, in that purchase_indentno auto increment ....
@Second Table is purchase_item table in that i save purchase_indentno and purchase_itemid is auto increment column
Now i want get sum of quantity for all item based on purchase_indentno and project_id,
for example.....
in purchase_item table have two purchase_indentno 1 and 2, for purchase_indentno->1 i purchased 3 items but purchase_indentno->2 i purchased 2 items only ,now i give purchase_indentno->2 means, i want to sum of items quantity purchased before purchase_indentno->2 and item quantity sum only what are item presents in purchase_indentno->2
I used following Query but sum of all item quantity where purchase_indentno->1,
declare @projectid int
set @projectid=(select project_id from purchase where purchase_indentno=1)
select SUM(purchase_item.quantity) as totalqty from purchase_item inner join
purchase on purchase_item.purchase_indentno=purchase.purchase_indentno and
purchase.project_id=@projectid and purchase.purchase_indentno < 2 group by purchase_item.item_id
My results as...
2
5
5
but i expect
2
5
because purchase_indentno->2 only have 2 items....please help me
Thanks,
My results as... 2 5 5 but i expect 2 5 because purchase_indentno->2 only have 2 items....please help me Thanks,
Again you have shared lot of information , but none that anyone here could use to understand your requirement or problem.
A typical question should include DDL(your table structure), sample data and expected output, a brief business logic if needed
probably your query have wrong group by clause as I mentioned in my earlier post.
DDL....
---Purchase_item table....
CREATE TABLE [dbo].[purchase_item](
[purchase_itemid] [int] IDENTITY(1,1) NOT NULL,
[purchase_indentno] [int] NULL,
[item_id] [int] NULL,
[quantity] [int] NULL,
[amount] [money] NULL,
[ref_id] [int] NULL,
[purchase_status] [int] NULL,
[purchase_date] [date] NULL
) ON [PRIMARY]
GO
purchase table...
CREATE TABLE [dbo].[purchase](
[purchase_indentno] [int] IDENTITY(1,1) NOT NULL,
[purchase_date] [date] NULL,
[project_id] [int] NULL,
[user_id] [int] NULL,
[supplier_id] [int] NULL,
[itemcategory] [nvarchar](80) NULL,
[paymentmode] [nvarchar](50) NULL,
[ref_id] [int] NULL,
[totalamount] [money] NULL,
[ar_indenterid] [int] NULL,
[ar_indenterdate] [datetime] NULL,
[ar_indenterapprove] [int] NULL,
[ar_accountsid] [int] NULL,
[ar_accountsdate] [datetime] NULL,
[ar_accapprove] [int] NULL,
[ar_adminid] [int] NULL,
[ar_admindate] [datetime] NULL,
[ar_adminapprove] [int] NULL,
[ar_mdid] [int] NULL,
[ar_mddate] [datetime] NULL,
[ar_mdapprove] [int] NULL,
[purchasedby] [int] NULL,
[purchasedate] [datetime] NULL,
[purchaseapprove] [int] NULL,
[received] [int] NULL,
[ar_verifyid] [int] NULL,
[ar_verify] [int] NULL,
[ar_verifydate] [datetime] NULL
) ON [PRIMARY]
GO
Sample data....
Now i want get sum of quantity for all item based on purchase_indentno and project_id,
for example.....
in purchase_item table have two purchase_indentno 1 and 2, for purchase_indentno->1 i purchased 3 items but purchase_indentno->2 i purchased 2 items only ,now i give purchase_indentno->2 means, i want to sum of items quantity purchased before purchase_indentno->2 and item quantity sum only what are item presents in purchase_indentno->2
I used following Query but sum of all item quantity where purchase_indentno->1,
declare @projectid int
set @projectid=(select project_id from purchase where purchase_indentno=1)
select SUM(purchase_item.quantity) as totalqty from purchase_item inner join
purchase on purchase_item.purchase_indentno=purchase.purchase_indentno and
purchase.project_id=@projectid and purchase.purchase_indentno < 2 group by purchase_item.item_id
My results as...
2
5
5
but i expect
2
5
because purchase_indentno->2 only have 2 items....please help me
Thanks,