Solved

SQL Query to Sum based on more then 1 col

Posted on 2012-12-24
2
523 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
0
Comment
Question by:zolf
2 Comments
 
LVL 9

Accepted Solution

by:
keyu earned 500 total points
ID: 38719755
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
 

Author Closing Comment

by:zolf
ID: 38720612
thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Bulk load data error 5 34
mySql Syntax 7 32
How to calculate iops? 12 27
SQL 2016 Setup - Connectivity Issues 4 16
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now