select distinct and grouping - 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

SQL Server 2005 Forums

Transact-SQL (2005)

select distinct and grouping

Author

Topic

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-02-01 : 11:28:02

hallo

pls help with query.

TabSale
item...name...price
1.......a......2
1.......a......1
1.......a......2
2.......b......3
2.......b......2
2.......b......3

TabGroup
item........grp
1...........222
2...........222

result:

grp....name....price1
222.....b........13

description result:
select distinct grp=222 (item 1,2 is join with grp 222)
name=b because product "b" has price > name "a" (b=8, a=5)
price1=13 sum price for group 222

thanks for help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 12:17:44

[code]
SELECT grp,name,GPrice
FROM
(
SELECT tg.grp,ti.name,ti.price,ROW_NUMBER() OVER (PARTITION BY tg.grp ORDER BY ti.price desc) AS rn,SUM(Price) OVER (PARTITION BY tg.grp) AS GPrice
FROM TabGroup tg
INNER JOIN (SELECT item,name,SUM(Price) AS Price
FROM TabSale
GROUP BY item,name
)ts
ON ts.item = tg.item
)t
WHERE rn=1
[/code]

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-02-01 : 13:13:58

visakh16
As always, thank you very

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 13:26:03

welcome

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-02-01 : 16:54:59

quote:

[i]Originally posted by visakh16


SELECT grp,name,GPrice
FROM
(
SELECT tg.grp,ti.name,ti.price,ROW_NUMBER() OVER (PARTITION BY tg.grp ORDER BY ti.price desc) AS rn,SUM(Price) OVER (PARTITION BY tg.grp) AS GPrice
FROM TabGroup tg
INNER JOIN (SELECT item,name,SUM(Price) AS Price
            FROM TabSale
            GROUP BY item,name
            )ts
ON ts.item = tg.item
)t
WHERE rn=1

when I add one column:

TabSale
item...name...price...town
1.......a......2......888
1.......a......1......888
1.......a......2......444
2.......b......3......444
2.......b......2......444
2.......b......3......444

but following query returned:
grp....name....price1....town2
222.....b........13.......1(correctly is 2) it is count distinct

where is error please?

SELECT grp,name,GPrice,town1
FROM
(
SELECT tg.grp,ti.name,ti.price,town1,ROW_NUMBER() OVER (PARTITION BY tg.grp ORDER BY ti.price desc) AS rn,SUM(Price) OVER (PARTITION BY tg.grp) AS GPrice, COUNT(DISTINCT town1) OVER (PARTITION BY tg.grp) as town2
FROM TabGroup tg
INNER JOIN (SELECT item,name,SUM(Price) AS Price, COUNT(DISTINCT town) as town1
FROM TabSale
GROUP BY item,name
)ts
ON ts.item = tg.item
)t
WHERE rn=1

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 17:15:50

[code]
SELECT grp,name,GPrice,town1
FROM
(
SELECT tg.grp,ti.name,ti.price,town1,ROW_NUMBER() OVER (PARTITION BY tg.grp ORDER BY ti.price desc) AS rn,SUM(Price) OVER (PARTITION BY tg.grp) AS GPrice, SUM(town1) OVER (PARTITION BY tg.grp) as town2
FROM TabGroup tg
INNER JOIN (SELECT item,name,SUM(Price) AS Price, COUNT(DISTINCT town) as town1
FROM TabSale
GROUP BY item,name
)ts
ON ts.item = tg.item
)t
WHERE rn=1

[/code]

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-02-01 : 17:22:38

one mistake...please edit
visakh16 your query returned town 3 (correctly is 2)

TabSale
item...name...price...town
1.......a......2......888
1.......a......1......888
1.......a......2......444
2.......b......3......444
2.......b......2......444
2.......b......3......444
your query:
item 1 has select distinct town = 2 (888 and 444)
item 2 has select distinct town = 1 (444)
result your query is 3, The result should be correctly 2 (town 888 and 444)
Can you edit query please.?

kiran.bala
Starting Member

1 Post

Posted - 2012-02-02 : 07:30:59

Select g.grp,(Select name from TabSale group by name having
SUM(price)in(
(Select max(a.price) price1 from
(select SUM(price)price from TabSale group by name )a)))Name,
SUM(s.price)Price1
from TabSale S join TabGroup G on s.item = G.item
Group by g.grp

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-02-02 : 08:06:26

thanks kiran.bala
but it is no good
your result is:

grp......name....price
222.......a.......13
town missing

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 11:49:58

[code]
SELECT grp,name,GPrice,town1
FROM
(
SELECT tg.grp,ti.name,ti.price,town1,ROW_NUMBER() OVER (PARTITION BY tg.grp ORDER BY ti.price desc) AS rn,SUM(Price) OVER (PARTITION BY tg.grp) AS GPrice, MAX(town1) OVER (PARTITION BY tg.grp) as town2
FROM TabGroup tg
INNER JOIN (SELECT item,name,SUM(Price) AS Price, COUNT(DISTINCT town) as town1
FROM TabSale
GROUP BY item,name
)ts
ON ts.item = tg.item
)t
WHERE rn=1

[/code]

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-02-02 : 14:30:32

thanks visakh16
I do not understand it is wrong somewhere ..... Town returned different numbers

TabSale
item...name...price...town
1.......a......2......888
1.......a......1......888
1.......a......2......444
2.......b......3......444
2.......b......2......777
2.......b......3......777
2.......b......2......777
2.......b......3......555

result:
grp....name....gprice....town1
222.....b.......18.......4 (but your query return for example 3 or 2)

I hope this is well described

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 14:34:21

can you tell how town will be 4? it should be 5 isnt it?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 14:52:12

ok got it
i think this is what you want?


;With Group_Details
AS
(
SELECT tg.grp,item,name,Price,town
FROM TabGroup tg
INNER JOIN (SELECT item,name,SUM(Price) AS Price,town
FROM TabSale
GROUP BY item,name,town
)ts
ON ts.item = tg.item
)

SELECT gd.grp,gd.item,gd1.Price,gd1.town1
FROM Group_Details gd 
INNER JOIN (SELECT grp,SUM(Price) AS Price, COUNT(DISTINCT town) AS town1,MAX(Price) AS MPrice
            FROM Group_Details
            GROUP BY grp) gd1
On gd1.grp = gd.grp
AND gd1.MPrice = gd.Price

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-02-02 : 14:57:42

quote:
Originally posted by visakh16

can you tell how town will be 4? it should be 5 isnt it?

4 it is, it is count distinct town for group 222
item 1 and 2 is grp 222 (first topic)
it means for grp 222 town1 will 4
price not affecting town1
understant?

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-02-02 : 15:53:08

thats it and sorry for the misunderstanding
Thank you for your patience, visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 18:21:52

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources