请考虑下表
tweets --------------------------------- tweet_id nyse_date class type --------------------------------- 1 2011-03-12 2 0 2 2011-03-12 1 1 3 2011-03-12 1 0 4 2011-03-12 1 0 5 2011-03-12 0 0 6 2011-03-12 1 0 7 2011-03-12 3 2 8 2011-03-12 3 0每条推文都分配了一个'class',它是1,2或3,'type'是0,1或2.我想概述每个类和类型中的推文数量,如下所示:
nyse_date class1 class2 class3 type0 type1 type2 ------------------------------------------------------- 2011-03-12 3 1 2 6 1 1我开始使用以下查询(按日期分组,因为真实表有许多不同的日期):
SELECT nyse_date, COUNT(class) AS class1 FROM tweets WHERE class = 1, COUNT(class) AS class2 FROM tweets WHERE class = 2 GROUP BY nyse_date ASC预期的产出是
nyse_date class1 class2 -------------------------- 2011-03-12 3 1但我得到了一个错误。 我相信这与我每个查询只能使用一个WHERE子句(我使用的是2)这一事实有关,但我不知道替代方案是什么。 你做?
任何帮助是极大的赞赏 :-)
Consider the following table
tweets --------------------------------- tweet_id nyse_date class type --------------------------------- 1 2011-03-12 2 0 2 2011-03-12 1 1 3 2011-03-12 1 0 4 2011-03-12 1 0 5 2011-03-12 0 0 6 2011-03-12 1 0 7 2011-03-12 3 2 8 2011-03-12 3 0Each tweet has assigned a 'class', which is either 1, 2 or 3 and a 'type', being 0, 1 or 2. I want to have an overview of the number of tweets in each class and type, as follows:
nyse_date class1 class2 class3 type0 type1 type2 ------------------------------------------------------- 2011-03-12 3 1 2 6 1 1I started with the following query (grouping by date because the real table has many different dates):
SELECT nyse_date, COUNT(class) AS class1 FROM tweets WHERE class = 1, COUNT(class) AS class2 FROM tweets WHERE class = 2 GROUP BY nyse_date ASCThe expected output was
nyse_date class1 class2 -------------------------- 2011-03-12 3 1but I got an error instead. I believe this has to do with the fact that I can only use one WHERE clause per query (and I'm using 2) but I don't know what the alternative would be. Do you?
Any help is greatly appreciated :-)
最满意答案
尝试这个:
SELECT nyse_date, SUM(class = 1) AS class1, SUM(class = 2) AS class2, SUM(class = 3) AS class3 FROM tweets GROUP BY nyse_date ASCclass = 1是一个布尔表达式,它的值为TRUE或FALSE 。 TRUE等于1 。 因此,使用SUM实际上意味着“计算条件保持的所有行”
Try this:
SELECT nyse_date, SUM(class = 1) AS class1, SUM(class = 2) AS class2, SUM(class = 3) AS class3 FROM tweets GROUP BY nyse_date ASCThe class = 1 is a boolean expression, which evalues to TRUE or FALSE. A TRUE equals 1. Hence using SUM practically means "count all rows for which condition holds"
更多推荐
发布评论