CODEWITHSUNDEEP
sqlsql-server
Mark Hill
Mark Hill

Reputation: 1839

Getting Totals correctly with SQL SERVER

I'm trying to figure out how to get the proper totals for my SQL Query, here's what I have below

QUERY: 

SELECT po_num, order_date, order_by, vendor, total, outstanding FROM(
SELECT p.id as po_num, p.order_date, p.order_by, v.name as vendor, SUM(i.qty*i.price) AS total, i.qty - ISNULL(SUM(r.qty), 0) AS outstanding FROM
    Parts.dbo.po_item i
    INNER JOIN Parts.dbo.po p
    ON i.po__id = p.id
    INNER JOIN Parts.dbo.vendor v
    ON p.vendor__id = v.id
    LEFT OUTER JOIN Parts.dbo.receipts r
    ON r.po_item__id = p.id AND r.qty = i.qty
    GROUP BY p.id, i.qty, i.price, p.order_date, p.order_by, v.name
) AS tbl

RESULT:

=============================================================================================
po_num | order_date                 | order_by  | vendor              |  total  | outstanding
============================================================================================
500000 |    2014-11-07 09:00:31.057 |  mhill    | Kenworth            |   0     |     6
500000 |    2014-11-07 09:00:31.057 |  mhill    | Kenworth            |  8.75   |     7
500000 |    2014-11-07 09:00:31.057 |  mhill    | Kenworth            |   0     |     8
500000 |    2014-11-07 09:00:31.057 |  mhill    | Kenworth            |  949.8  |    12
500001 |    2014-11-07 09:03:09.533 |  mhill    | Max I Walker        |  159.6  |     8
500002 |    2014-11-07 09:08:03.180 |  mhill    | Standard Battery    |   0     |     8
500002 |    2014-11-07 09:08:03.180 |  mhill    | Standard Battery    |   0     |    25
500003 |    2014-11-07 09:10:28.320 |  mhill    | Tires 4 Hire        |  31.25  |    25
500004 |    2014-11-07 09:28:47.813 |  mhill    | Cashman Caterpiller | 143.6   |     8
500009 |    2014-11-07 10:38:48.463 |  mhill    | Kelly's Body Shop   | 14999.5 |    10
500012 |    2014-11-10 13:04:56.320 |  mhill    | MHC Truck           | 639.6   |    8
500012 |    2014-11-10 13:04:56.320 |  mhill    | MHC Truck           |  4995   |   100

What I want it to do is give me one row for each po_num with a total, and an outstanding, so I only want to have 7 rows instead of the 12 that it pulled. I'll continue working on this. If someone could explain what I'm doing wrong that'd be fantastic!

Upvotes: 1

Views: 50

Answers (2)

M. Page
M. Page

Reputation: 2814

I supposed you want the max of outstanding.

SELECT po_num, order_date, order_by, vendor, SUM(total), max(outstanding) 
FROM(
   SELECT p.id as po_num, p.order_date, p.order_by, v.name as vendor, SUM(i.qty*i.price) AS total, i.qty - ISNULL(SUM(r.qty), 0) AS outstanding FROM
    Parts.dbo.po_item i
    INNER JOIN Parts.dbo.po p
    ON i.po__id = p.id
    INNER JOIN Parts.dbo.vendor v
    ON p.vendor__id = v.id
    LEFT OUTER JOIN Parts.dbo.receipts r
    ON r.po_item__id = p.id AND r.qty = i.qty
    GROUP BY p.id, i.qty, i.price, p.order_date, p.order_by, v.name
) 
GROUP BY po_num, order_date, order_by, vendor

Upvotes: 0

Steven Eccles
Steven Eccles

Reputation: 239

Assuming you want the total and outstanding amounts added together for each purchase order:

SELECT po_num, order_date, order_by, vendor, SUM(total), SUM(outstanding)
FROM (YOUR SUBQUERY - OMITTED FOR BREVITY)
GROUP BY po_num, order_date, order_by, vendor

Upvotes: 2

Related Questions