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
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 ;
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.Re
dbo.Tafsil.description,
sum(dbo.ProductBoughtSuppl
dbo.Batch.batchNumber
FROM
dbo.ProductBoughtSupplierD
INNER JOIN
dbo.ProductBoughtSupplier
ON
(
dbo.ProductBoughtSupplierD
INNER JOIN
dbo.Receipt
ON
(
dbo.ProductBoughtSupplier.
INNER JOIN
dbo.Branch
ON
(
dbo.Receipt.targetBranchId
INNER JOIN
dbo.Batch
ON
(
dbo.ProductBoughtSupplierD
INNER JOIN
dbo.Tafsil
ON
(
dbo.Batch.productId = dbo.Tafsil.id)
WHERE
dbo.Receipt.receiptType = 5
AND dbo.ProductBoughtSupplier.
group by dbo.Branch.id,
dbo.Branch.name,
CONVERT(VARCHAR(10),dbo.Re
dbo.Tafsil.description,
dbo.Batch.batchNumber
SELECT
dbo.Branch.id,
dbo.Branch.name,
CONVERT(VARCHAR(10),dbo.Re
dbo.Tafsil.description,
sum(dbo.ProductBoughtSuppl
dbo.Batch.batchNumber
FROM
dbo.ProductBoughtSupplierD
INNER JOIN
dbo.ProductBoughtSupplier
ON
(
dbo.ProductBoughtSupplierD
INNER JOIN
dbo.Receipt
ON
(
dbo.ProductBoughtSupplier.
INNER JOIN
dbo.Branch
ON
(
dbo.Receipt.targetBranchId
INNER JOIN
dbo.Batch
ON
(
dbo.ProductBoughtSupplierD
INNER JOIN
dbo.Tafsil
ON
(
dbo.Batch.productId = dbo.Tafsil.id)
WHERE
dbo.Receipt.receiptType = 5
AND dbo.ProductBoughtSupplier.
group by dbo.Branch.id,
dbo.Branch.name,
CONVERT(VARCHAR(10),dbo.Re
dbo.Tafsil.description,
dbo.Batch.batchNumber
Premium Content
You need an Expert Office subscription to comment.Start Free Trial