Query to display distinct combination and then display count for each distinct combination

Question

There is a transid field for which multiple rows will exist in this table with a list of config values. For all the transid's, I would like to retrieve all the distinct combination of transid, config_name and value fields that exist in the table group by count

I have join query which is not returning the result as expected. Below is the table structure, query used, result and expected result

Table

transid            config_name               value
1                   payment_fee                instant
2                   eligible_account           true
1                   Block_intl_trans           false
5                   payment_fee                provider_charge
1                   eligible_account           false  
1                         KycEligible               0
2                         KycEligible               1
5                        KycEligible                1
5                   Block_intl_trans            true
2                   Block_intl_trans            false   
2                   payment_fee                 provider_charge
5                   eligible_account            true 

The above table structure implies that below are the combination of configuration values for each user.

transid  KycEligible  payment_fee     eligible_account     Block_intl_trans
1      0            instant          false                false
2      1            provider_charge  true                 false
5      1            provider_charge  true                 false       

                   

Below is the query that I used to Convert rows to columns and then group them per config_name(For every config_name, instead of multiple rows for each config key and value combination). Then select all distinct combination of KycEligible, configname and value combination that are present in the table and count of each distinct combination.

select
    distinct
    max(case when b.config_name = 'KycEligible' then b.config_value end) KycEligible,
    max(case when b.config_name = 'payment_fee' then b.config_value end)  payment_fee,
    max(case when b.config_name = 'eligible_account' then b.config_value end)  eligible_account,
    max(case when b.config_name = 'Block_intl_trans' then b.config_value end)  Block_intl_trans,
    count(*) AS COUNT
from tableA b
where b.config_name in ('KycEligible', 'payment_fee', 'eligible_account', 'Block_intl_trans')
group by b.config_name
having count(*) > 1

Expected Result:

KycEligible  payment_fee     eligible_account     Block_intl_trans  Count
    0            instant          false                false             1
    1            provider_charge  true                 false             2 

My query is not returning the expected result. Can someone please help with this query?

Answer 1

I'm giving two fiddle example because the example data you provide is not consistent.

1. This fiddle is for transid=5 with Block_intl_trans data is true - consistent with your table data example: https://www.db-fiddle.com/f/r1imsYP8dQxkLSo5SkYcVK/3
2. This fiddle is for transid=5 with Block_intl_trans data is false - consistent with the configuration combination that you illustrate: https://www.db-fiddle.com/f/r1imsYP8dQxkLSo5SkYcVK/4

I'm guessing the unique combination would be coming from all of the config_name values. Here is the example query:

SELECT KycEligible, payment_fee, eligible_account, Block_intl_trans, COUNT(*) FROM
(SELECT transid,
       GROUP_CONCAT(CASE WHEN config_name="KycEligible" THEN config_value END) AS "KycEligible",
       GROUP_CONCAT(CASE WHEN config_name="payment_fee" THEN config_value END) AS "payment_fee",
       GROUP_CONCAT(CASE WHEN config_name="eligible_account" THEN config_value END) AS "eligible_account",
       GROUP_CONCAT(CASE WHEN config_name="Block_intl_trans" THEN config_value END) AS "Block_intl_trans"
 FROM TableA
 GROUP BY transid) V
GROUP BY KycEligible, payment_fee, eligible_account, Block_intl_trans;

Answer 2

I think you need something like this:

SELECT
  KycEligible, payment_fee, eligible_account, Block_intl_trans, COUNT(*) CNT
FROM (
  SELECT
    (SELECT MAX(t0.config_value) FROM test t0 WHERE t0.config_name = 'KycEligible' AND t0.transid = t.transid) as KycEligible,
    (SELECT MAX(t0.config_value) FROM test t0 WHERE t0.config_name = 'payment_fee' AND t0.transid = t.transid) as payment_fee,
    (SELECT MAX(t0.config_value) FROM test t0 WHERE t0.config_name = 'eligible_account' AND t0.transid = t.transid) as eligible_account,
    (SELECT MAX(t0.config_value) FROM test t0 WHERE t0.config_name = 'Block_intl_trans' AND t0.transid = t.transid) as Block_intl_trans
  FROM
    test t
  GROUP BY t.transid
) dt
GROUP BY KycEligible, payment_fee, eligible_account, Block_intl_trans
;

This would give the following:

ycEligible  payment_fee         eligible_account    Block_intl_trans    CNT
0           instant○            false               false                   1
1           provider_charge     true                false                   1
1           provider_charge     true                true                    1

The result differs from expected in question as:

2 and 5 transids have different Block_intl_trans
1 and 5 transids have different payment_fee
1 and 2 transids have different eligible_account

Answer 3

Simple query with Max,
count in result can be based on your input data (transid=5, config=Block_intl_trans, value=false or value=true)

SELECT KycEligible, payment_fee, eligible_account, Block_intl_trans, COUNT(*) FROM
( 
SELECT 
       transid,
       max(CASE WHEN config_name="KycEligible" THEN value END) AS "KycEligible",
       max(CASE WHEN config_name="payment_fee" THEN value END) AS "payment_fee",
       max(CASE WHEN config_name="eligible_account" THEN value END) AS "eligible_account",
       max(CASE WHEN config_name="Block_intl_trans" THEN value END) AS "Block_intl_trans"
 FROM <YOUR_TABLE_NAME>
 GROUP BY transid
 ) tmp
GROUP BY KycEligible, payment_fee, eligible_account, Block_intl_trans;