SQL Query to Sum based on more then 1 col

Hello there,

I have this query which returns me the qty of each product in each office with their dates.now in the returned result i have repeated products with the same date in the same branch. i want to sum those records. how can i do it. I am using MSSQL 2008



SELECT
    dbo.Branch.id,
    dbo.Branch.name,
    CONVERT(VARCHAR(10),dbo.Receipt.receiptDate,105) AS SaleDate,
    dbo.Tafsil.description,
    dbo.ProductBoughtSupplierDetail.quantity,
    dbo.Batch.batchNumber
FROM
    dbo.ProductBoughtSupplierDetail
INNER JOIN
    dbo.ProductBoughtSupplier
ON
    (
        dbo.ProductBoughtSupplierDetail.productBoughtSupplierId = dbo.ProductBoughtSupplier.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.ProductBoughtSupplier.warehouseReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.targetBranchId = dbo.Branch.id)
INNER JOIN
    dbo.Batch
ON
    (
        dbo.ProductBoughtSupplierDetail.batchId = dbo.Batch.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
WHERE
    dbo.Receipt.receiptType = 5
AND dbo.ProductBoughtSupplier.invoiceReceiptId IS NULL ;

Open in new window

q6.gif
zolfAsked:
Who is Participating?
 
keyuConnect With a Mentor Commented:
you can use group by as you are having all other fields same except qty so your query will be

SELECT
    dbo.Branch.id,
    dbo.Branch.name,
    CONVERT(VARCHAR(10),dbo.Receipt.receiptDate,105) AS SaleDate,
    dbo.Tafsil.description,
    sum(dbo.ProductBoughtSupplierDetail.quantity) as quantity,
    dbo.Batch.batchNumber
FROM
    dbo.ProductBoughtSupplierDetail
INNER JOIN
    dbo.ProductBoughtSupplier
ON
    (
        dbo.ProductBoughtSupplierDetail.productBoughtSupplierId = dbo.ProductBoughtSupplier.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.ProductBoughtSupplier.warehouseReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.targetBranchId = dbo.Branch.id)
INNER JOIN
    dbo.Batch
ON
    (
        dbo.ProductBoughtSupplierDetail.batchId = dbo.Batch.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
WHERE
    dbo.Receipt.receiptType = 5
AND dbo.ProductBoughtSupplier.invoiceReceiptId IS NULL
group by dbo.Branch.id,
    dbo.Branch.name,
    CONVERT(VARCHAR(10),dbo.Receipt.receiptDate,105),
    dbo.Tafsil.description,
    dbo.Batch.batchNumber
0
 
zolfAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.