Netezza聚合问题(数据透视)

问题描述

我正在使用Netezza生成报告，并且有以下查询

I'm using Netezza to generate a report and I have the following query

select  b.group_name,a.group_id, PASS_FAIL, COUNT(*) from log a
join group b on a.group_id=b.group_id
group by b.group_name,a.group_id, PASS_FAIL

它在如下表中收集有关通过和失败次数的信息:

It collects information on the number of passes and failures in a table like this:

    GROUP_NAME  GROUP_ID    PASS_FAIL   COUNT
    GROUP1        4            FAIL     382
    GROUP1        4            PASS     339

如何更改查询，以便可以将PASS和FAIL用作列.

How can I change the query so that I can use PASS and FAIL as the columns.

GROUP_NAME  GROUP_ID      PASS      FAIL
GROUP1        4           339       382

枢轴在Netezza中不可用，所以我想知道实现此目标的最佳方法是什么.最好的方法是什么?

Pivot isn't available in Netezza so I was wondering what the best way to go about this would be. What is the best way to do this?

推荐答案

我没有netezza的直接经验，但据我所知，它支持SUM/CASE的标准SQL解决方案

I don't have direct experience with netezza but from what I've read it supports the standard SQL solution of SUM/CASE

SELECT 
    GROUP_NAME,
    GROUP_ID,
    SUM(CASE WHEN PASS_FAIL = 'PASS' THEN 1 ELSE 0 END) as PASS,
    SUM(CASE WHEN PASS_FAIL = 'FAIL' THEN 1 ELSE 0 END) as FAIL
FROM 
    log a
    join group b 
    on a.group_id=b.group_id
GROUP BY 
    b.group_name,
    a.group_id

这篇关于Netezza聚合问题(数据透视)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！