Pls advise on query.. many thanks!!

Question

Post reply

Pls advise on query.. many thanks!!

wilmoslee 83606

SSC Rookie

Points: 40
More actions
March 27, 2010 at 11:47 pm

#220957

Hi all,
pls kindly advise on my query question below..
Table One: Item . Column : ItemCode (pk), ItemDesc...
Table two: ItemDet. Column : ItemCode (fK), WarehouseCode, ItemQty, ItemUnitCost...
My query:
Select I.ItemCode, I.ItemDesc, ID.ItemQty, ID.ItemUnitCost, (ID.ItemQty x ID.ItemUnitCost) AS LineTotal
FROM Item I
LEFT JOIN ItemDet ID ON ID.ItemCode = I.ItemCode
The problem is.. I need to include one grand total for the linetotal column ... may i know how the query will be?
Many thks in adv.

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

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

wilmoslee 83606 SSC Rookie Points: 40 More actions · Answer 1

wilmoslee 83606

SSC Rookie

Points: 40

March 28, 2010 at 12:56 am

#1141035

hi.. really no one can help?? 🙁

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Dear Will, please go through the following article by Jeff http://www.sqlservercentral.com/articles/Best+Practices/61537/ on forum etiquettes..

the information u have provided is not sufficient for us to help u... please post the table structures, sample rows, ur input and ur expected output... this will help us a lot..

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

wilmoslee 83606 (3/28/2010)
hi.. really no one can help?? 🙁

This is a forum, not a chat room. Be patient. Expecting someone to attend to your query in an hour over a weekend is a little unrealistic.

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150468 More actions · Answer 4

Bored, and feeling psychic. Try:

DECLARE @Item

TABLE (

code CHAR(3) NOT NULL PRIMARY KEY,

item_desc VARCHAR(10) NOT NULL

);

DECLARE @Detail

TABLE (

code CHAR(3) NOT NULL,

quantity INTEGER NOT NULL,

unit_cost SMALLMONEY NOT NULL

);

INSERT @Item

(code, item_desc)

VALUES ('ABC', 'ABC desc'),

('DEF', 'DEF desc'),

('GHI', 'GHI desc');

INSERT @Detail

(code, quantity, unit_cost)

VALUES ('ABC', 5, $1),

('GHI', 3, $4);

SELECT I.code,

I.item_desc,

D.quantity,

D.unit_cost,

line_total = SUM(D.quantity * D.unit_cost)

FROM @Item I

LEFT

JOIN @Detail D

ON D.code = I.code

GROUP BY

ROLLUP ((I.code, I.item_desc, D.quantity, D.unit_cost));

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

bartusp SSC Enthusiast Points: 166 More actions · Answer 5

TRY THE FOLLOWING:

Select I.ItemCode, I.ItemDesc, ID.ItemQty, ID.ItemUnitCost, SUM(ID.ItemQty x ID.ItemUnitCost) AS LineTotal

FROM Item I

LEFT JOIN ItemDet ID ON ID.ItemCode = I.ItemCode

GROUP BY I.ItemCode, I.ItemDesc, ID.ItemQty, ID.ItemUnitCost

Paul White SSC Guru Points: 150468 More actions · Answer 6

bartusp (3/29/2010)
TRY THE FOLLOWING:
Select I.ItemCode, I.ItemDesc, ID.ItemQty, ID.ItemUnitCost, SUM(ID.ItemQty x ID.ItemUnitCost) AS LineTotal
FROM Item I
LEFT JOIN ItemDet ID ON ID.ItemCode = I.ItemCode
GROUP BY I.ItemCode, I.ItemDesc, ID.ItemQty, ID.ItemUnitCost

That looks very similar to my post - except it does not create the requested grand total.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

bartusp SSC Enthusiast Points: 166 More actions · Answer 7

Hi There,

Ye i did not read your reply, only after i posted i have yours. But cool stuff, at least he got his answer.

Regards,