Solved

SQL Query to Sum based on more then 1 col

Posted on 2012-12-24

1 solution

532 Views

Last Modified: 2012-12-25

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

Comment

Question by:zolf

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

2 Comments

LVL 9

Accepted Solution

by:keyu

keyu earned 500 total points

ID: 387197552012-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

Author Closing Comment

by:zolf

ID: 387206122012-12-25

thanks

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to recover deleted rows in SQL Server

Article by: Yashwant

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.

Detect locking/blocking in SQL and send email alerts

Article by: lcohan

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…