I have a table, TableA with amongst other fields, a field for Qty.
Qty can range from 0 to 100.
How do I count the number of rows with a qty between 1 and 10, 11 and
20, 21 and 30, and so on using one SQL statement?

Regards,
Ciarán

(chudson007@hotmail.com) writes:
> I have a table, TableA with amongst other fields, a field for Qty.
> Qty can range from 0 to 100.
> How do I count the number of rows with a qty between 1 and 10, 11 and
> 20, 21 and 30, and so on using one SQL statement?

SELECT qty10, COUNT(*)
FROM (SELECT qty10 = ((qty - 1) / 10) * 10 + 1
FROM tbl) AS ds
GROUP BY qty10


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

That seems to just count the number of times each qty appears, just
like

SELECT qty AS Expr1, COUNT(*) AS Expr2
FROM [Temp]
GROUP BY qty


How should I change it so that it counts the the number of qtys between
each range of 10?

Regards,
Ciarán

Hi Erland Sommarskog ,

You Always give helpfull and informative answers.
I changed the query a bit to show the LowRange as well HiRange
SELECT LowRange,HiRange,COUNT(*)
FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
,HiRange=((qty - 1) / 10) * 10 + 10
FROM sales) AS ds
GROUP BY lowRange ,HiRange
but I am facing a problem can You guide me on this

This query {select q=qty+10 from sales order by q} works but
{select q=qty+10 from sales group by q} does not work .SQL Server2000
is not recognising Aliased Columns in second case .
--------
With regards
Jatinder Singh (System Analyst )

(chudson007@hotmail.com) writes:
> That seems to just count the number of times each qty appears, just
> like
>
> SELECT qty AS Expr1, COUNT(*) AS Expr2
> FROM [Temp]
> GROUP BY qty
>
>
> How should I change it so that it counts the the number of qtys between
> each range of 10?

The query I posted was:

SELECT qty10, COUNT(*)
FROM (SELECT qty10 = ((qty - 1) / 10) * 10 + 1
FROM tbl) AS ds
GROUP BY qty10

I would expect to give the desired result, assuming that qty is integer.
If qty is float or decimal, it will indeed just be a roundabout way to
count single qtys.

I will have to admit that I did not test my query, but there is standard
recommendation that posting asking for help with queries should include:

o CREATE TABLE statement for your table(s).
o INSERT statement with sample data.
o The desired output given the sample data.

This makes it very easy for me or anyone else who anser to cut and paste
into Query Analyzer and test whatever we post.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

jsfromynr (jatinder.singh@clovertechnologies.com) writes:
> You Always give helpfull and informative answers.
> I changed the query a bit to show the LowRange as well HiRange
> SELECT LowRange,HiRange,COUNT(*)
> FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
> ,HiRange=((qty - 1) / 10) * 10 + 10
> FROM sales) AS ds
> GROUP BY lowRange ,HiRange
> but I am facing a problem can You guide me on this
>
> This query {select q=qty+10 from sales order by q} works but
> {select q=qty+10 from sales group by q} does not work .SQL Server2000
> is not recognising Aliased Columns in second case .

Correct. I believe that Access does this, but that's not in alignment with
the SQL standards.

Instead, the technique to use is a derived table as a above.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks Erland,
I am facing another problem of displaying a summarised data along with
the detail data
i.e

Item Qty

Item1 10
Item1 10
Item1 20
40 ( Sum for Item1)
and so on ............

I wish to have a single query which runs on all RDBMS . Is it possible
?

USE pubs
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type

This Query works but it would work on MS SQLServer .

-------------------------------------------------
With regards
Jatinder Singh (System Analyst )

jsfromynr (jatinder.singh@clovertechnologies.com) writes:
> I am facing another problem of displaying a summarised data along with
> the detail data
> i.e
>
> Item Qty
>
> Item1 10
> Item1 10
> Item1 20
> 40 ( Sum for Item1)
> and so on ............
>
> I wish to have a single query which runs on all RDBMS . Is it possible
> ?
>
> USE pubs
> SELECT type, price, advance
> FROM titles
> ORDER BY type
> COMPUTE SUM(price), SUM(advance) BY type
>
> This Query works but it would work on MS SQLServer .

Here is a query which I believe should be fairly portable. (But since
I only work with SQL Server, I can make no warranties):

SELECT type, x = '', price, advance
FROM titles
UNION
SELECT type, 'Total', SUM(price), SUM(advance)
FROM titles
GROUP BY type
ORDER BY type, x

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland Sommarskog wrote:
> jsfromynr (jatinder.singh@clovertechnologies.com) writes:
> > I am facing another problem of displaying a summarised data along
with
> > the detail data
> > i.e
> >
> > Item Qty
> >
> > Item1 10
> > Item1 10
> > Item1 20
> > 40 ( Sum for Item1)
> > and so on ............
> >
> > I wish to have a single query which runs on all RDBMS . Is it
possible
> > ?
> >
> > USE pubs
> > SELECT type, price, advance
> > FROM titles
> > ORDER BY type
> > COMPUTE SUM(price), SUM(advance) BY type
> >
> > This Query works but it would work on MS SQLServer .
>
> Here is a query which I believe should be fairly portable. (But since
> I only work with SQL Server, I can make no warranties):
>
> SELECT type, x = '', price, advance
> FROM titles
> UNION
> SELECT type, 'Total', SUM(price), SUM(advance)
> FROM titles
> GROUP BY type
> ORDER BY type, x
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Hi Erland,
Thanks ,I think it will work on any RDBMS . Your analysis ablity is
really something. I cannot describe it in words.

Thanks Again
With warm regards
Jatinder Singh (System Analyst)

Hi Erland,

Can we similarly replace CUBE operator in SQL Server by using simple
queries that will run on any RDBMS?

With warm regards
Jatinder Singh (System Analyst)