I have Problem to get Sum of Quantity in SQL Server 2008

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

February 11th, 2014 1:42am

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

Free Windows Admin Tool Kit Click here and download it now
February 11th, 2014 1:54am

its come sum all the item quantity...result as 12.. but i want 2,5....because only two item present purchase_indentno=2..

Thanks,

February 11th, 2014 1:59am

Can you post table structure, sample data+expected results? That's much easier to understand
Free Windows Admin Tool Kit Click here and download it now
February 11th, 2014 2:08am

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,

February 11th, 2014 2:53am

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.



Free Windows Admin Tool Kit Click here and download it now
February 11th, 2014 4:06am

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,




February 11th, 2014 6:01am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics