为直方图创建数据。 [英] creating data for a histogram.

问题描述

我有一张桌子，TableA和其他字段，一个数量字段。

数量范围从0到100.

我如何计算数量qty在1到10之间的行，11和

20,21和30，依此类推，使用一个SQL语句？


问候，
$ b $bCiarán

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

推荐答案

（ch ********@hotmail.com）写道：

(ch********@hotmail.com) writes:

我有一个表，TableA与其他字段，一个数量字段。
数量范围从0到100 。
如何计算qty在1到10,11和20,201和30之间的行数，依此类推，使用一个SQL语句？

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， es **** @ sommarskog.se


SQL Server SP3的联机书籍
http://www.microsoft.com/sql/techinf...2000/books .asp

这似乎只计算了t的数量imes每个数量出现，只需

喜欢


SELECT数量AS Expr1，COUNT（*）AS Expr2

FROM [Temp]

GROUP BY数量

如何更改它以便计算每个范围为10的

之间的qtys数？

问候，
$ b $bCiarán

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

嗨Erland Sommarskog，

您总是提供有用且信息丰富的答案。

我更改了查询以显示LowRange以及HiRange

SELECT LowRange，HiRange，COUNT（* ）

FROM（SELECT lowRange =（（qty - 1）/ 10）* 10 + 1

，HiRange =（（qty - 1）/ 10）* 10 + 10

来自销售）AS ds

GROUP BY lowRange，HiRange

但是我遇到了问题你可以指导我这个吗？ >

此查询{从q按销售订单选择q =数量+10}有效但

{从q按销售组选择q =数量+10}不起作用.SQL Server2000

不是识别在第二种情况下使用别名列。

--------

关于

Jatinder Singh（系统分析师）

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 )

这篇关于为直方图创建数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！