summary of data - 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

summary of data

Author

Topic

Randjana
Starting Member

9 Posts

Posted - 2011-05-16 : 13:15:23

hi all,

i have a table with serials.
exmaple:

apples 459786
apples 459787
apples 459788
apples 459789
bananas 458923
bananas 458924
bananas 458925
bananas 458926
bananas 458927

my query result should be

4, apples, begin_serial = 459786 ,end_serial = 459789
5 ,bananas, begin_serial = 458923, end_serial = 458927

is it possible to achieve this with a query?

Any help will be appreciated.

kind regards,

Randjana

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-16 : 13:17:54

[code]SELECT COUNT(*), fruit_name, MIN(serial_no), MAX(serio_no) FROM yourTable GROUP BY fruit_name
[/code]

Randjana
Starting Member

9 Posts

Posted - 2011-05-16 : 13:26:52

Thanks for the quick reply.

but what in case if i have another range op apples.

exmaple:
apples 459786
apples 459787
apples 459788
apples 459789
bananas 458923
bananas 458924
bananas 458925
bananas 458926
bananas 458927
apples 439788
apples 439789

result should be :
4, apples, begin_serial = 459786 ,end_serial = 459789
5 ,bananas, begin_serial = 458923, end_serial = 458927
2, apples, begin_serial = 439788 ,end_serial = 439789

thanks in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-16 : 13:40:02

I am assuming you are on SQL 2005 or higher. If so,


;WITH cte AS
(
SELECT
   *,
   serial_no - ROW_NUMBER() OVER (PARTITION BY fruit_name ORDER BY serial_no) AS grp_no
FROM
   fruit_table
)
SELECT 
   COUNT(*),
   fruit_name,
   MIN(serial_no),
   MAX(serial_no)
FROM
   cte
GROUP BY
   fruit_name,
   grp_no

Randjana
Starting Member

9 Posts

Posted - 2011-05-16 : 14:39:11

Many Many thanks!! It worked!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-16 : 19:40:20

Glad it did, and you are quite welcome!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources