Solved

SQL Query to Sum based on more then 1 col

Posted on 2012-12-24

1 solution

Medium Priority

534 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 2000 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 dynamically return a table structure using a Stored Procedure

Article by: Ryan Chong

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…

Export Table Data in SQL Server Management Studio

Article by: Dirk

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.

Dealing with Dates: Data Types and String Conversions (SQL Server)

Video by: Steve

Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.