在单个查询中获取多列计数 [英] Get Multi Columns Count in Single Query

问题描述

我正在开发一个应用程序，我需要在表上编写查询，该查询将在单个查询中返回多列计数.

I am working on a application where I need to write a query on a table, which will return multiple columns count in a single query.

经过研究，我能够为单个 sourceId 开发查询，但是如果我想要多个 sourceId 的结果会发生什么.

After research I was able to develop a query for a single sourceId, but what will happen if i want result for multiple sourceIds.

select '3'as sourceId,
   (select count(*) from event where sourceId = 3 and plateCategoryId = 3) as TotalNewCount,
   (select count(*) from event where sourceId = 3 and plateCategoryId = 4) as TotalOldCount;

我需要获取多个源 ID 的 TotalNewCount 和 TotalOldCount，例如 (3,4,5,6)

I need to get TotalNewCount and TotalOldCount for several source Ids, for example (3,4,5,6)

任何人都可以帮忙，我如何修改我的查询以返回三列的结果集，包括列表 (3,4,5,6) 中所有来​​源的数据

Can anyone help, how can I revise my query to return a result set of three columns including data of all sources in list (3,4,5,6)

谢谢

推荐答案

您可以一次性完成所有源 ID:

You can do all source ids at once:

select source_id
       sum(case when plateCategoryId = 3 then 1 else 0 end) as TotalNewCount,
       sum(case when plateCategoryId = 4 then 1 else 0 end) as TotalOldCount
from event
group by source_id;

如果要限制源 ID，请使用 where(在 group by 之前).

Use a where (before the group by) if you want to limit the source ids.

注意:以上在 Vertica 和 MySQL 中都有效，作为标准 SQL 应该可以在任何数据库中使用.

Note: The above works in both Vertica and MySQL, and being standard SQL should work in any database.

这篇关于在单个查询中获取多列计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！