Hey guys,

This is a very very simple problem... Can anyone please help me with
this sql?
I'm sure some of you would have come across this problem. I'm hitting
a wall here...

----------------------My select command Begin...--------------
Select User, Item, Price, Qty, (Price * Qty) as Total
From MyTable
----------------------My select command End...--------------


----------------------My result set begin...--------------
user item price qty total
-------------------------------------
Raghu xxx p1 1 t1 <---- t1 = sum for all Raghu items
Raghu yyy p2 2 t1 <---REPETITION - NOT TO BE SHOWN
Raghu zzz p3 2 t1 <---REPETITION - NOT TO BE
SHOWN

Velu yyy p2 2 t2 <---- t2 = sum for all velu items
Velu zzz p3 5 t2 <---REPETITION - NOT TO BE SHOWN

----------------------My result set END...--------------


----------------My Question--------------

What I basically want is to display the item total for Raghu, Velu
etc. only once.

so, my DREAM result set should be

----------------------My DREAM RESULT SET BEGIN...--------------
user item price qty total
-------------------------------------
Raghu xxx p1 1 t1 <---- t1 = sum for all Raghu items
Raghu yyy p2 2 t1 <--- t1 = REPETITION - NOT TO BE SHOWN
Raghu zzz p3 2 t1 <--- t1 = REPETITION - NOT TO
BE SHOWN

Velu yyy p2 2 t2 <---- t2 = sum for all velu items
Velu zzz p3 5 t2 <---- t2 = REPETITION - NOT TO BE SHOWN
----------------------My DREAM RESULT SET END...--------------


Any help is greatly appreciated.

Thanks in advance.

Hi

This seems like something that should be left to a report writing front end
and not the database query!

It is always best to post DDL (CREATE TABLE etc) statenebts for your tables,
and example data (as Insert statements along with the expected output and
obtained outputs.

One possible solution would be:

CREATE TABLE MyTable ( [User] VARCHAR(5), Item CHAR(3), Price int, Qty int )

INSERT INTO MyTable ( [User], Item, Price, Qty ) VALUES ( 'Raghu', 'xxx', 1,
1 )
INSERT INTO MyTable ( [User], Item, Price, Qty ) VALUES ( 'Raghu', 'yyy', 2,
2 )
INSERT INTO MyTable ( [User], Item, Price, Qty ) VALUES ( 'Raghu', 'zzz', 3,
2 )
INSERT INTO MyTable ( [User], Item, Price, Qty ) VALUES ( 'Velu', 'yyy', 2,
2 )
INSERT INTO MyTable ( [User], Item, Price, Qty ) VALUES ( 'Velu', 'zzz', 3,
5 )

Select [User], Item, Price, Qty, (Price * Qty) as Total
From MyTable

/*
User Item Price Qty Total
----- ---- ----------- ----------- -----------
Raghu xxx 1 1 1
Raghu yyy 2 2 4
Raghu zzz 3 2 6
Velu yyy 2 2 4
Velu zzz 3 5 15
*/

Select [User], Item, Price, Qty, (SELECT SUM(Price * Qty) FROM MyTable T
WHERE t.[User] = m.[User] ) as Total
From MyTable M
ORDER BY [User],item,price
/*
User Item Price Qty Total
----- ---- ----------- ----------- -----------
Raghu xxx 1 1 11
Raghu yyy 2 2 11
Raghu zzz 3 2 11
Velu yyy 2 2 19
Velu zzz 3 5 19

*/

Select [User], Item, Price, Qty, (SELECT SUM(Price * Qty) FROM MyTable T
WHERE t.[User] = m.[User] ) as Total
From MyTable M
WHERE NOT EXISTS ( SELECT 1 FROM MyTable p WHERE p.[User] = m.[User] and
p.item < m.item and p.price < m.price )
ORDER BY [User],item,price

/*
User Item Price Qty Total
----- ---- ----------- ----------- -----------
Raghu xxx 1 1 11
Velu yyy 2 2 19
*/

Select [User], Item, Price, Qty, (SELECT SUM(Price * Qty) FROM MyTable T
WHERE t.[User] = m.[User] ) as Total
From MyTable M
WHERE NOT EXISTS ( SELECT 1 FROM MyTable p WHERE p.[User] = m.[User] and
p.item < m.item and p.price < m.price )
UNION ALL
Select [User], Item, Price, Qty, NULL
From MyTable M
WHERE EXISTS ( SELECT 1 FROM MyTable p WHERE p.[User] = m.[User] and p.item
< m.item and p.price < m.price )
ORDER BY [User],item,price

/*
User Item Price Qty Total
----- ---- ----------- ----------- -----------
Raghu xxx 1 1 11
Raghu yyy 2 2 NULL
Raghu zzz 3 2 NULL
Velu yyy 2 2 19
Velu zzz 3 5 NULL
*/


John

[quoted text, click to view]