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

Question

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:

removing item4 from the group by gives me an error "Selected non-aggregate values must be part of the associated group."
PIVOT is not an option in my version of teradata
I have also tried a subquery of

    (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."

score 1 · Accepted Answer · answered Apr 06 '21 at 00:26

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;

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

1 Answers1