在单个查询中获取多列计数

本文介绍了在单个查询中获取多列计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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.