CODEWITHSUNDEEP
mysqlsql
Gelo
Gelo

Reputation: 105

Using group_concat With a Condition: Display 0 if Values are Not Equal

I just want to display column fields horizontally but also putting a condition to it. Display zero if it has not met the condition.

Example problem: Find the PAYCODE 912 and 686 and display the amount, if not available, display 0

my_table

EMPLOYEE PAYCODE AMOUNT
1 912 1
1 123 3
2 912 5
2 686 7
3 111 4

Output must be:

EMPLOYEE AMOUNT
1 1,0
2 5,7
3 0,0

My code so far:

SELECT 
    EMPLOYEE,
    GROUP_CONCAT(DISTINCT CONCAT(
          IF(PAYCODE = '912', AMOUNT, '0'), 
          IF(PAYCODE = '686', AMOUNT, '0')) 
          SEPARATOR',') as AMOUNT
FROM 
    my_table

Note: There are no duplicate paycodes on a similar employee. e.g. two 912 paycodes

Upvotes: 0

Views: 56

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

I'm thinking a cross join approach should work here:

SELECT e.EMPLOYEE,
       GROUP_CONCAT(COALESCE(t.AMOUNT, 0) ORDER BY e.PAYMENTTYPE DESC) AS AMOUNT
FROM (SELECT DISTINCT EMPLOYEE FROM my_table) e
CROSS JOIN (SELECT '912' AS PAYMENTTYPE UNION ALL SELECT '686') p
LEFT JOIN my_table t
    ON t.EMPLOYEE = e.EMPLOYEE AND
       t.PAYMENTTYPE = p.PAYMENTTYPE
GROUP BY e.EMPLOYEE;

The cross join between the e and p subqueries generates all employee/payment type combinations of interest (only types 912 and 686). We then left join to your table to bring in the amounts, which if are missing we report 0 instead.

Upvotes: 1

Related Questions