Query.. Pls help!!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query.. Pls help!!!!

Author

Topic

wil-ki
Starting Member

2 Posts

Posted - 2010-03-28 : 02:48:32

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-28 : 02:52:53

you mean you need a row with grandtotal value? or you want it as a separate column with each row?

Also, are you using sql 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wil-ki
Starting Member

2 Posts

Posted - 2010-03-28 : 02:55:27

hi visakh16, yes.. I need a row with the grandtotal value.

i am using sql 2008...

please kindly advise..

many many thks...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-28 : 02:58:49

try:-


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
GROUP BY I.ItemCode, I.ItemDesc, ID.ItemQty, ID.ItemUnitCost
WITH ROLLUP

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-03-28 : 03:56:44

Since you are using SQL 2008 you can use the new grouping set clause.


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 GROUPING sets(I.ItemCode, I.ItemDesc, ID.ItemQty, ID.ItemUnitCost)

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources