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-01-29 : 09:08:43

Hallo

I have follow problem..

TabSales
item....date.......town...cust....price1....price2
1....2011-02-10.....10....400........3........1
1....2011-03-25.....20....400........2........1
1....2011-04-06.....20....500........1........2
a2...2011-02-15.....40....100........4........2
a2...2011-09-16.....40....100........4........2
3....2011-03-29.....10....300........1........1
3....2011-07-31.....20....500........3........2
3....2011-07-10.....40....600........3........3

RESULT: result is "select distinct" for "item"

TabResult
item..frequency..nummonths..numtown..numcust..price1....price2
1...........3.............3...........2........2.........6...........4
a2..........2.............2...........1........1.........8...........4
3...........3.............2...........3........3.........7...........6

description:

look at item for example "3"..
frequency = is 3 (item is three count)
nummonths = there are 2 months (march,july)
numtown = there are 3 town(10,20,40)
numcust = there are 3 customers (300.500.600),item a2 has 1 numcust(100)
price1 = sum (1+3+3=7)
price1 = sum (1+2+3=6)

it is possible?

big thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 14:22:06

[code]
SELECT item,
COUNT(date) AS frequency,
COUNT(DISTINCT MONTH(date)) AS nummonths,
COUNT(DISTINCT town) AS numtown,
COUNT(DISTINCT cust) AS numcust,
SUM(price1) AS price1,
SUM(price2) AS price2
FROM table
GROUP BY item
[/code]

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-01-29 : 14:27:44

thanks visakh16
my respect . .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 14:35:16

welcome
try and let us know the outcome

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-01-29 : 14:58:16

result?

of course good. No°1
I just switched "COUNT(date) AS frequency" on "COUNT(item) AS frequency"
query is really simple, I need to learn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 14:59:17

welcome...
This will give you good start on GROUP BY

http://www.w3schools.com/sql/sql_groupby.asp

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-01-29 : 15:01:40

yes
thanks visakh16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources