Query

ColumnX holds values between 1001 and 5999. How can i make a query to get th
at result set:
Group Quantity
1000s xxx
2000s xxx
3000s xxx
4000s xxx
5000s xxxSELECT ColumnX / 1000 AS grp, SUM(qty) AS totalqty
FROM T1
GROUP BY ColumnX / 1000
BG, SQL Server MVP
www.SolidQualityLearning.com
"LacOniC" <iletisim@.bigfoot.com> wrote in message
news:uHy2j$HNFHA.3076@.tk2msftngp13.phx.gbl...
> ColumnX holds values between 1001 and 5999. How can i make a query to get
that result set:
> Group Quantity
> 1000s xxx
> 2000s xxx
> 3000s xxx
> 4000s xxx
> 5000s xxx
>|||Try,
select min(colA), count(*)
from table1
group by colA / 1000
order by min(colA);
AMB
"LacOniC" wrote:

> ColumnX holds values between 1001 and 5999. How can i make a query to get
that result set:
> Group Quantity
> 1000s xxx
> 2000s xxx
> 3000s xxx
> 4000s xxx
> 5000s xxx
>
>|||Correction,
select min((colA / 1000) * 1000), count(*)
from table1
group by colA / 1000
order by min((colA / 1000) * 1000);
AMB
"Alejandro Mesa" wrote:
> Try,
> select min(colA), count(*)
> from table1
> group by colA / 1000
> order by min(colA);
>
> AMB
> "LacOniC" wrote:
>|||Try
Select Str((ColumnX / 1000) + 1, 2,0) + '000s' As Group,
Count(*)
From <TableName>
Group By Str((ColumnX / 1000) + 1, 2,0) + '000s'
"LacOniC" wrote:

> ColumnX holds values between 1001 and 5999. How can i make a query to get
that result set:
> Group Quantity
> 1000s xxx
> 2000s xxx
> 3000s xxx
> 4000s xxx
> 5000s xxx
>
>|||sorry, leae out the + 1
Select Str((ColumnX / 1000), 3,0) + '000s' As Group,
Count(*)
From <TableName>
Group By Str((ColumnX / 1000), 3,0) + '000s'
"LacOniC" wrote:

> ColumnX holds values between 1001 and 5999. How can i make a query to get
that result set:
> Group Quantity
> 1000s xxx
> 2000s xxx
> 3000s xxx
> 4000s xxx
> 5000s xxx
>
>|||This works very well. Thank you.|||Thak you. I got an error:
Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'Group'.
Select Str((M_AGE / 1000), 3,0) + '000s' As Group, Count(*)
From FORUM_MEMBERS
Group By Str((M_AGE / 1000), 3,0) + '000s'|||Thank you. I tried your query and:
SELECT M_AGE / 1000 AS grp, SUM(qty) AS totalqty
FROM FORUM_MEMBERS
GROUP BY M_AGE / 1000
Server: Msg 207, Level 16, State 3, Line 7
Invalid column name 'qty'.|||I forgot to say, data type is nvarchar, so can't use SUM already. Sorry.