SUM PRICE AND QUNATITY

Maneesh A N

13y1.3kVisual Basic .NET

I NEED TO SUM ITEM QUANTITY,ITEM PRICE DATE WISE AND ALSO CUSTOMER NAME WISE IN REPORT,SQL 2005...REPLY SOON PLEASE

ALTER Procedure[dbo].[PrcInvoiceItemPrint]

(

-- @iSalesID int,

-- @vAccName varchar,

@iAccID int,

@FromDate DateTime

-- @ToDate DateTime

)

SET NOCOUNT ON

BEGIN

--select dInvoiceDate from tblSales where dInvoiceDate=@FromDate

--select tblSalesItem.iItemID, Max(tblSalesItem.vTea), SUM(tblSalesItem.iNos)

--FROM tblSales INNER JOIN

--tblSalesItem ON tblSales.iSalesID = tblSalesItem.iSalesID

--where dInvoiceDate=@FromDate

--group by tblSalesItem.iItemID

select tblSales.iAccID, tblSales.vAccName,tblSalesItem.iItemID, tblSalesItem.deAmount,tblSalesItem.vTea,tblSalesItem.iNos,tblSales.dInvoiceDate

FROM tblSales INNER JOIN

tblSalesItem ON tblSales.iSalesID = tblSalesItem.iSalesID

where tblSales.iAccID=@iAccID AND tblSales.dInvoiceDate=Convert(varchar,@FromDate,101)

--group by tblSalesItem.iItemID

end

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi13y ago
agree with Jaganathan but some correction.
select Convert(varchar,tblSales.dInvoiceDate,101), tblSales.iAccID, tblSales.vAccName, tblSalesItem.iItemID, sum(tblSalesItem.deAmount) as 'Total Amount', sum(tblSalesItem.iNos) as 'Quantity'
FROM tblSales INNER JOIN
tblSalesItem ON tblSales.iSalesID = tblSalesItem.iSalesID
where tblSales.iAccID=@iAccID AND tblSales.dInvoiceDate=Convert(varchar,@FromDate,101) and tblSalesItem.vTea = 'Tea'
group by Convert(varchar,tblSales.dInvoiceDate,101), tblSales.iAccID , tblSales.vAccName
hope this will help.
0
Jaganathan Bantheswaran13y ago
Hi,

Try like this,

select tblSales.dInvoiceDate, tblSales.iAccID, tblSales.vAccName, tblSalesItem.iItemID, sum(tblSalesItem.deAmount) as 'Total Amount', sum(tblSalesItem.iNos) as 'Quantity'
FROM tblSales INNER JOIN
tblSalesItem ON tblSales.iSalesID = tblSalesItem.iSalesID
where tblSales.iAccID=@iAccID AND tblSales.dInvoiceDate=Convert(varchar,@FromDate,101) and tblSalesItem.vTea = 'Tea'
group by tblSalesItem.dInvoiceDat, tblSales.iAccID , tblSales.vAccName

you have to query the DB twise. first one for Coffie & second one for Tea
0
Maneesh A N13y ago
While calculating the sum value by joining two or more tables, what are the columns we need to mention in the Group By clause and why do we need to give those columns?
0
Maneesh A N13y ago
TEA COFFEE

Date Quantity Total Amount Quantity Total Amount

28-10-2013 5 50 6 120

SUPPOSE WE TAKE TEA OR COFFEE 5 TIMES IN A SAME DAY,BUT ITS DIFFERENT TIMING,MEANS EACH TIME WE SAVES INVOICE.BUT IN CRYSTAL REPORT WE SHOULD GET TOGETHER MENTIONED AS ABOVE.
0
Jaganathan Bantheswaran13y ago
Hi,

What is your problem?

Can you post that how your output should be?
0