Trouble calculating a column (need some help)

Question

Post reply

Trouble calculating a column (need some help)

bennett_js

SSC Journeyman

Points: 82
More actions
September 23, 2011 at 7:56 am

#248361

I have two tables:
EQUIPMENT
Id - type INT (PK)
Quantity - type INT
Data for EQUIPMENT table:
211 1000
212 2000
213 3000
214 4000
215 5000
216 6000
217 7000
EQUIPMENT_QUANTITY
Id - type INT (FK to EQUIPMENT.Id)
Quantity_Used - type INT
Data for EQUIPMENT_USED table:
211 50
211 50
212 100
212 200
217 75
217 30
217 20
217 10
212 20
212 30
211 40
211 20
Here my SELECT statement:
SELECT EQUIPMENT.EQUIPMENT_ID, EQUIPMENT.QUANTITY AS QUANTITY_ON_HAND, SUM(EQUIPMENT_USED.QUANTITY_USED) AS QUANTITY_USED
FROM EQUIPMENT INNER JOIN
EQUIPMENT_USED ON EQUIPMENT.EQUIPMENT_ID = EQUIPMENT_USED.EQUIPMENT_ID
GROUP BY EQUIPMENT.EQUIPMENT_ID, EQUIPMENT.QUANTITY
Results:
EQUIPMENT_ID QUANTITY_ON_HAND QUANTITY_USED
211 1000 160
212 2000 350
217 7000 135
**Here's where I need some help. I need to calculate a fourth column named QUANTITY_RECEIVED which is (QUANTITY_ON_HAND + QUANTITY_USED)
Ex from above result set:
sum(1000 + 160)
sum(2000 + 350)
sum(7000 + 135)
Thanks. Appreciated.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

So where are you having issues? You seem to know what you are doing!

bennett_js SSC Journeyman Points: 82 More actions · Answer 2

The issue is when I try to run statement:

SELECT PWARD_EQUIPMENT.EQUIPMENT_ID, PWARD_EQUIPMENT.AMMO_QUANTITY AS QUANTITY_ON_HAND,

SUM(PWARD_AMMO_USE_EQUIPMENT.QUANTITY_USED) AS QUANTITY_USED, SUM(PWARD_EQUIPMENT.AMMO_QUANTITY + QUANTITY_USED) AS QUANTITY_RECEIVED

FROM PWARD_EQUIPMENT INNER JOIN

PWARD_AMMO_USE_EQUIPMENT ON PWARD_EQUIPMENT.EQUIPMENT_ID = PWARD_AMMO_USE_EQUIPMENT.EQUIPMENT_ID

GROUP BY PWARD_EQUIPMENT.EQUIPMENT_ID, PWARD_EQUIPMENT.AMMO_QUANTITY

I get the following result:

21110001604160

21220003508350

217700013528135

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

The extra join multiplies the rows so you add too much.

You can simply do an outer apply or use a derived table to preagregate that data, then left join to it (in case no data exists).

bennett_js SSC Journeyman Points: 82 More actions · Answer 4

Not quite sure I follow. Can you provide the SQL statements required for your two recommended solutions?

Thanks.

bennett_js SSC Journeyman Points: 82 More actions · Answer 5

Can anyone help show me the SQL required to reslove my issue? I've never used an OUTER APPLY before.

Appreciated.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

bennett_js (9/23/2011)
The issue is when I try to run statement:
SELECT PWARD_EQUIPMENT.EQUIPMENT_ID, PWARD_EQUIPMENT.AMMO_QUANTITY AS QUANTITY_ON_HAND,
SUM(PWARD_AMMO_USE_EQUIPMENT.QUANTITY_USED) AS QUANTITY_USED, SUM(PWARD_EQUIPMENT.AMMO_QUANTITY + QUANTITY_USED) AS QUANTITY_RECEIVED
FROM PWARD_EQUIPMENT INNER JOIN
PWARD_AMMO_USE_EQUIPMENT ON PWARD_EQUIPMENT.EQUIPMENT_ID = PWARD_AMMO_USE_EQUIPMENT.EQUIPMENT_ID
GROUP BY PWARD_EQUIPMENT.EQUIPMENT_ID, PWARD_EQUIPMENT.AMMO_QUANTITY
I get the following result:
21110001604160
21220003508350
217700013528135

The original tables you gave didn't match the original query you gave and now you're changing the problem again. 😉

Sure, we could "eyeball" a solution for you and it might be wrong because we don't have the data that you do. Please take a look at the first link in my signature line below and use the methods in that article to post some readily consumable data so we can help you more quickly and accurately. Thanks.

In the meantime and getting back to your original post... here's the best way to post some readily consumable data...

--===================================================================

-- Create the test tables for demonstration purposes.

-- Nothing is this section is actually a part of the solution.

-- We're just creating test data here.

--===================================================================

--===== Conditionally drop the test tables to make

-- reruns in SSMS easier

IF OBJECT_ID('tempdb..#Equipment','U') IS NOT NULL

DROP TABLE #Equipment

;

IF OBJECT_ID('tempdb..#Equipment_Used','U') IS NOT NULL

DROP TABLE #Equipment_Used

;

GO

--===== Create the test tables

-- (FK not used because of Temp Table demonstration)

CREATE TABLE #Equipment

(

Equipment_ID INT PRIMARY KEY CLUSTERED,

Quantity INT NOT NULL

)

;

CREATE TABLE #Equipment_Used

(

Equipment_ID INT,

Quantity_Used INT

)

;

--===== Populate the test tables

INSERT INTO #Equipment

(Equipment_ID, Quantity)

SELECT 211,1000 UNION ALL

SELECT 212,2000 UNION ALL

SELECT 213,3000 UNION ALL

SELECT 214,4000 UNION ALL

SELECT 215,5000 UNION ALL

SELECT 216,6000 UNION ALL

SELECT 217,7000

;

INSERT INTO #Equipment_Used

(Equipment_ID, Quantity_Used)

SELECT 211,50 UNION ALL

SELECT 212,100 UNION ALL

SELECT 212,200 UNION ALL

SELECT 217,75 UNION ALL

SELECT 217,30 UNION ALL

SELECT 217,20 UNION ALL

SELECT 217,10 UNION ALL

SELECT 212,20 UNION ALL

SELECT 212,30 UNION ALL

SELECT 211,40 UNION ALL

SELECT 211,20

;

... and here's one possible solution to your original problem that uses that test data...

--===== Solution to the original problem given

WITH

cteTotalUsed AS

(

SELECT onhand.Equipment_ID,

onhand.Quantity AS Quantity_On_Hand,

SUM(ISNULL(used.Quantity_Used,0)) AS Quantity_Used

FROM #Equipment onhand

LEFT JOIN #Equipment_Used used

ON onhand.Equipment_ID = used.Equipment_ID

GROUP BY onhand.Equipment_ID, onhand.Quantity

)

SELECT Equipment_ID,

Quantity_On_Hand,

Quantity_Used,

(Quantity_On_Hand + Quantity_Used) AS Quantity_Received

FROM cteTotalUsed

;

The method above is sometimes referred to as "pre-aggregation" so that aggregated columns that are used more than once only need to be created once.

If you'll post readily consumable data that matches the table names and column names for the latest problem you've just posted, I'm sure someone would be happy to respond with a working/tested code example.

As a side bar, I'd recommend not used either all upper case or all lower case in code just for the sake of readability. I'd also recommend not using table names when identifying table columns for two reasons... First, that's been deprecated by Microsoft in favor of the proper use of table aliases and, second, use of the right table aliases can make the code even easier to read and understand.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bennett_js SSC Journeyman Points: 82 More actions · Answer 7

Hi Jeff. Thanks for the example. It works well. I read through the articles you suggested. And now have a much better understanding of what's required and where you guys are coming from. I'll adhere to the recommendations for future postings. Please know it was not my intention to upset anyone. I was simply trying to summarize the tables and attributes involved.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

bennett_js (9/26/2011)
Hi Jeff. Thanks for the example. It works well. I read through the articles you suggested. And now have a much better understanding of what's required and where you guys are coming from. I'll adhere to the recommendations for future postings. Please know it was not my intention to upset anyone. I was simply trying to summarize the tables and attributes involved.

Absolutely understood. That's why I took the time to explain. If I was upset about it, I wouldn't have posted a solution never mind an indepth one. 🙂 Thank you for taking it all the right way.

While we're here... any questions on the code any of us posted for your problem or anything else?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)