mysql - 建议我在 MySql 中查询计数——我没有得到正确的输出

Question

我在 MySQL 中有一张表，如下所示。

State marketId 
CA     Ind
CO     Ind
CA     Grp
CA     Grp

我想选择数据作为计数和总计数(应该如下所示)。

State marketId Count totalCountInd
  CA     Ind    1        2               
  CO     Ind    1

目前我正在使用以下查询。那没有给出正确的输出。

select state,marketId,count(*),sum(CASE marketID WHEN 'ind' THEN 1 ELSE 0 END) AS totalCountInd from BatchReport where marketId='ind' group by state,marketId;
+-------+----------+----------+---------------+
| state | marketId | count(*) | totalCountInd |
+-------+----------+----------+---------------+
| CA    | in       |        1 |               |
| CO    | in       |        1 |               |
|+-------+----------+----------+---------------+

Answer 1

这会给你一个更接近你想要的结果不同之处在于它会重复 totalCountInd

SELECT State, marketid, COUNT(1) as totalCount,
(
    SELECT COUNT(1) FROM BatchReport
    WHERE marketid = bp.marketid
) AS totalCountInd
FROM BatchReport bp
WHERE marketid = 'Ind'
GROUP BY State, marketid

结果是这样的:

State   marketid    totalCount  totalCountInd
CA      Ind         1           2
CO      Ind         1           2