mysql - GROUP BY 在 SELECT 中选择

Question

我有一个表，我想选择一些行并按日期对它们进行分组。问题是当我在选择中选择该行时，该行似乎未按日期分组，请参阅示例:

SELECT date, SUM(price), (SELECT SUM(price), date FROM expense WHERE 
user = 'Catherine') as cprice FROM expense GROUP BY date

它不会对cprice进行分组，它会将所有Catherine价格相加，并且不会按日期进行分组，顺便说一句，我尝试将GROUP BY date放入选择范围内，但也不起作用。有什么建议吗？

expense table:
id   user      price   date
1    Frank      20     2014
2    Catherine  10     2013
3    Catherine  20     2014

谢谢

Answer 1

你可以试试这个:

SELECT 
  date, 
  SUM(price) as totalPrice, 
  SUM(IF(user='Catherine', price, 0)) as cprice 
FROM expense 
GROUP BY date

如果您想要两个价格在一起，那么您可以尝试以下操作:

SELECT 
  date, 
  SUM(price) + SUM(IF(user='Catherine', price, 0)) as cprice
FROM expense 
GROUP BY date