mysql - 在 mysql 中选择 Count 的查询

Question

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

State marketId 
CA     Ind
CO     Ind
CA     Grp
CA     Grp

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

State marketId Count totalCountInd  totalCountGrp
  CA     Ind    1        2               2
  CO     Ind    1
  CA     Grp    2

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

select state, marketId, count(*) as 'count', (select count(1) from BatchReport where marketId='Ind') as 'total' from  BatchReport group by state,marketId  having marketId='Ind'  
union  
select state, marketId, count(*) as 'count1', (select count(1) from BatchReport where marketId='Grp') as 'total1' from BatchReport group by state,marketId  having marketId='Grp'

请推荐我。

Answer 1

您想要实现的并不是使用 SQL 检索数据的好(且理想)方式:

您正在尝试同时检索两条不同的记录。您应该检索具有不同查询的那些:

首先检索每个州和 marketId 的计数:

SELECT State, marketId, COUNT(marketId) AS Count
FROM BatchReport
GROUP BY State, marketID

给你

| STATE | MARKETID | COUNT(MARKETID) |
--------------------------------------
|    CA |      Grp |               2 |
|    CA |      Ind |               1 |
|    CO |      Ind |               1 |

然后检索每个 marketId 的总计数:

SELECT
SUM(CASE MarketID WHEN 'Ind' THEN 1 ELSE 0 END) AS totalCountInd 
,SUM(CASE MarketID WHEN 'Grp' THEN 1 ELSE 0 END) AS totalCountGrp
FROM BatchReport

给你

| TOTALCOUNTIND | TOTALCOUNTGRP |
---------------------------------
|             2 |             2 |

或者您还可以获取行中每个 MarketId 的总计数，如下所示:

SELECT
MarketId, COUNT(MarketID) AS Count
FROM BatchReport
GROUP BY MarketId;

输出:

| MARKETID | COUNT |
--------------------
|      Grp |     2 |
|      Ind |     2 |

See this SQLFiddle