Group By Question

Last Post 27 Dec 2006 04:15 PM by JHunter. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
patrick351
New Member
New Member

--
27 Dec 2006 07:47 AM
Very simple situation. I have a table with 1 record and 1 column and that column contains the number 43.5

I need to get 4 records from this 1 record.

I need a record for every time the number 10 occurs within 43.5

We all know 10 goes into this 4 times with a remainder of 3.5 but I just can't think of a good way to write some kind of group by statement to pull this off.
JHunter
New Member
New Member

--
27 Dec 2006 04:15 PM
Hi,

Assuming you're looking for a record set with the number of rows equalling the result of a division by 10 ie 43.5 would yield 4 rows, 39 would yield 3 etc...

Try this:

Create a table called Numbers, CREATE TABLE Numbers ( number Int ); fill it with the numbers 1 though x (however large you need it to be), then its just a simple join:

SELECT val
FROM table_with_your_number
INNER JOIN numbers ON number <= val/10;

The numbers table is very useful to have "lying around"...!

Jamie
SQLUSA
New Member
New Member

--
07 Jan 2007 01:55 AM
True Jamie!

There is no easy way to gen a sequence in SQL Server 2005.

Here is one dynamic way: http://www.sqlusa.com/bestpractices...gsequence/

Kalman Toth
URL: http://www.sqlusa.com
JHunter
New Member
New Member

--
08 Jan 2007 02:29 PM
Guess what Itzik covered in this months SQL Mag!!!

Auxiliary Table-of-Numbers Tricks
http://www.sqlmag.com/Article/Artic...94376.html

Jamie
SQLUSA
New Member
New Member

--
13 Jan 2007 08:49 AM
Wow Jamie,

Hot topic!

I think in Oracle you can generate sequences without tricks.

Kalman Toth
SQLUSA: http://www.sqlusa.com
You are not authorized to post a reply.

Acceptable Use Policy