多输出一个SQL查询
我想写一个SQL查询,将输出多个变量,具体取决于数据库中存在特定数量的次数。这些信息将在图表中使用。多输出一个SQL查询
的DB设置如下:
ID | quantity | etc..
---------------------
1 | 2 |
2 | 6 |
3 | 1 |
4 | 2 |
.
.
.
这里是我进行的查询:
SELECT
COUNT(CASE WHEN quantity = 1) AS quantity1,
COUNT(CASE WHEN quantity = 2) AS quantity2
FROM orders
但我希望它输出这样的:
quantity1 | quantity2
---------------------
1 | 2 |
相反,我我得到这些错误信息:
在此先感谢!
编辑:如果你想在我的数据库测试你的代码,链接低于...
-Link被切除,
这里是另一种方法,也许它可以帮助:
SELECT SUM(CASE WHEN quantity = 1 THEN 1 ELSE 0 END) AS quantity_1,
SUM(CASE WHEN quantity = 2 THEN 1 ELSE 0 END) AS quantity_2,
SUM(CASE WHEN quantity = 3 THEN 1 ELSE 0 END) AS quantity_3,
SUM(CASE WHEN quantity = 4 THEN 1 ELSE 0 END) AS quantity_4,
SUM(CASE WHEN quantity = 5 THEN 1 ELSE 0 END) AS quantity_5,
SUM(CASE WHEN quantity = 6 THEN 1 ELSE 0 END) AS quantity_6,
SUM(CASE WHEN quantity > 6 THEN 1 ELSE 0 END) AS quantity_more_than_6
FROM orders
这里是MS Access中的一个特殊查询:
SELECT SUM(SWITCH(quantity = 1, 1)) AS quantity_1,
SUM(SWITCH(quantity = 2, 1)) AS quantity_2,
SUM(SWITCH(quantity = 3, 1)) AS quantity_3,
SUM(SWITCH(quantity = 4, 1)) AS quantity_4,
SUM(SWITCH(quantity = 5, 1)) AS quantity_5,
SUM(SWITCH(quantity = 6, 1)) AS quantity_6,
SUM(SWITCH(quantity > 6, 1)) AS quantity_more_than_6
FROM orders
我得到与OP中相同的错误,但看起来像在正确的轨道上。 IErrorInfo.GetDescription失败....这是什么meeean? – user3684557 2014-10-20 09:44:12
我已经将数据库文件添加到OP – user3684557 2014-10-20 10:38:30
看起来像你使用MS Access,它对吗?我更新了我的答案。 – DirkNM 2014-10-20 10:50:24
使用GROUP BY子句,是这样的:
SELECT quantity, COUNT(*) AS num
FROM Orders
GROUP BY quantity
HAVING COUNT(*) > 6;
编辑:您可以添加HAVING子句
这几乎完美!我计划在查询中做的是找到= 1,2,3,4,5,6以及6以上的任何数量。我该如何实现? – user3684557 2014-10-20 08:20:47
现在这**仅当计数> 6时显示。当数量为1,2,3,4,5,6 **时,需要数量> 6时需要**和**。 – user3684557 2014-10-20 09:45:48
我添加了数据库文件到OP – user3684557 2014-10-20 10:39:57
你的意思是做一个'组by'的数量可能。 – Rahul 2014-10-20 07:59:04