CODEWITHSUNDEEP
sqlpivotteradatatransposeteradata-sql-assistant
Ty Vmuch
Ty Vmuch

Reputation: 13

Teradata Rows to Columns (Need to Merge Doubled Row Count)

I have a query I'm running as follows -

SELECT 
item1,
item2,
item3,
CASE WHEN item4 = 'type A' THEN COUNT(DISTINCT field) END AS dcount_field_A,
CASE WHEN item4 = 'type B' THEN COUNT(DISTINCT field) END AS dcount_field_B,
FROM table
GROUP BY
item1
item2
item3
item4

and my output is:

Item1 Item2 Item 3 dcount_field_B dcount_field_A
Item1Value1 Item2Value1 Item3Value1 NULL 1234
Item1Value1 Item2Value1 Item3Value1 1234 NULL

but what I need is for these rows to be merged, like:

Item1 Item2 Item 3 dcount_field_B dcount_field_A
Item1Value1 Item2Value1 Item3Value1 1234 1234

Other attempted adjustments:

    (SELECT
    COUNT(DISTINCT(CASE WHEN item4 = 'type A' THEN field END)) AS dcount_field_A,
    COUNT(DISTINCT(CASE WHEN item4 = 'type B' THEN field END)) AS dcount_field_B
    FROM table
    GROUP BY item4),

and I get the error "Too many expressions in the select list of a subquery."

Upvotes: 1

Views: 148

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You want conditional aggregation. With count(distinct) this looks like:

SELECT item1, item2, item3,
       COUNT(DISTINCT CASE WHEN item4 = 'type A' THEN field END) AS dcount_field_A,
       COUNT(DISTINCT CASE WHEN item4 = 'type B' THEN field END) AS dcount_field_B,
FROM table
GROUP BY item1, item2, item3;

Upvotes: 1

Related Questions