Microsoft SQL Server
Microsoft SQL Server
Microsoft SQL Server 2008
Microsoft SQL Server 2008
Query Syntax
Query Syntax

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: 2012-12-24

Who is Participating?

Solutions

LVL 9

keyuConnect With a Mentor Commented: 2012-12-25

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