CODEWITHSUNDEEP
sqlsql-server-2008
The_DemoCorgin
The_DemoCorgin

Reputation: 744

Summing Qtys with Matching ID in multiple rows SQL

I have a sql query that returns a results table with IDs, Qtys, and Locations. An item can be in multiple locations, so there could be four rows for a single item, each row being its own location with its own qty. Example:

SELECT i.ITEM_NO, SUM(w.QTY_ON_HAND - w.QTY_DAMAGED) as QTY, w.LOCATION
FROM Workshop w
LEFT OUTER JOIN ITEM_LIST i
ON i.ITEM_ID = w.ITEM_ID
GROUP BY i.ITEM_NO, w.QTY_ON_HAND, w. QTY_DAMAGED, w.LOACTION

Returns:

-----------------------------------
|  ITEM_NO  |  QTY  |  LOCATION   | 
-----------------------------------
|  SN-15    |   0   |    SEA      |
-----------------------------------
|  SN-15    |   2   |    SFO      |
-----------------------------------
|  TY-14    |   1   |    SEA      |
-----------------------------------
|  TY-14    |   5   |    LAX      |
-----------------------------------
|  TY-14    |   0   |    SFO      |
-----------------------------------

What I want to do, ideally in this same query, is sum each location's qty to find the total qty for each ITEM_NO, with a results table looking like:

---------------------------
|  ITEM_NO  |  TOTAL_QTY  | 
---------------------------
|  SN-15    |       2     |
---------------------------
|  TY-14    |       6     |
---------------------------

What would be the best way to do this, and would I be able to do it all in one query? Thanks!

Upvotes: 1

Views: 120

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

This is a basic aggregation query. If you are going to use SQL effectively, you should learn the basics of the language:

select t.item_no, sum(t.qty) as total_qty
from table t
group by t.item_no;

group by is a fundamental part of the language.

EDIT:

Of course. The simplest way is to use with:

with t as (
      SELECT i.ITEM_NO, SUM(w.QTY_ON_HAND - w.QTY_DAMAGED) as QTY, w.LOCATION
      FROM Workshop w LEFT OUTER JOIN
           ITEM_LIST i
           ON i.ITEM_ID = w.ITEM_ID
      GROUP BY i.ITEM_NO, w.QTY_ON_HAND, w. QTY_DAMAGED, w.LOCATION
     )
select t.item_no, sum(t.qty) as total_qty
from table t
group by t.item_no;

But, this query doesn't really make sense. You don't need the two quantities in the group by. So, try this:

with t as (
      SELECT i.ITEM_NO, SUM(w.QTY_ON_HAND - w.QTY_DAMAGED) as QTY, w.LOCATION
      FROM Workshop w LEFT OUTER JOIN
           ITEM_LIST i
           ON i.ITEM_ID = w.ITEM_ID
      GROUP BY i.ITEM_NO, w.LOCATION
     )
select t.item_no, sum(t.qty) as total_qty
from table t
group by t.item_no;

And a further simplification is simply:

      SELECT i.ITEM_NO, SUM(w.QTY_ON_HAND - w.QTY_DAMAGED) as QTY
      FROM Workshop w LEFT OUTER JOIN
           ITEM_LIST i
           ON i.ITEM_ID = w.ITEM_ID
      GROUP BY i.ITEM_NO

Upvotes: 1

Dominique
Dominique

Reputation: 1119

SELECT ITEM_NO, SUM(TOTAL_QTY) AS TOTAL_QTY FROM tablename GROUP BY ITEM_NO

GROUP BY will separate the results per ITEM_NO... it might sound a bit confusing but does make sense.

EDIT: after extra question

SELECT ITEM_NO, SUM(TOTAL_QTY) AS TOTAL_QTY FROM
(
  SELECT i.ITEM_NO, SUM(w.QTY_ON_HAND - w.QTY_DAMAGED) as QTY, w.LOCATION
  FROM Workshop w
  LEFT OUTER JOIN ITEM_LIST i
  ON i.ITEM_ID = w.ITEM_ID
  GROUP BY i.ITEM_NO, w.QTY_ON_HAND, w. QTY_DAMAGED, w.LOACTION
) AS query1
GROUP BY ITEM_NO

What you're doing here is using the results of the query you made as the table to query the sums from. Every result of a query can be used as if it were a table in the database.

Upvotes: 1

Related Questions