MySQL Crosstab Aggregation with Two Conditions

Question

I have a query that creates a crosstab. Results are a counter txn_idfor branda, and a counter txn_idfor brandb.

txn_id is NOT UNIQUE. This is an example transaction table .:

txn_id | nationality_id | sku | sales | units

 1     |      1         |  1  |  20   | 2
 1     |      1         |  2  |  15   | 1
 2     |      4         |  1  |  20   | 2
 3     |      2         |  1  |  10   | 1
 4     |      3         |  2  |  15   | 1
 5     |      4         |  1  |  10   | 1

There are 2 more tables (products) - (sku, brand, product name)and (nationalities) - (nationality_id, nationality).

I would like to add a third column that will get me an account txn_idwhere BOTH brands are bought

The exit should be

nationality | branda | brandb | combined

  1         |  1     |   1    |   1
  2         |  1     |   0    |   0
  3         |  0     |   1    |   0
  4         |  2     |   0    |   0

Current request.

SELECT 
    nationalities.nationality,
    COUNT((CASE brand WHEN 'branda' THEN txn_id ELSE NULL END)) AS branda,
    COUNT((CASE brand WHEN 'brandb' THEN txn_id ELSE NULL END)) AS brandb
 <I want my 3rd column here>
FROM
    transaction_data
        INNER JOIN
    products USING (sku)
        INNER JOIN
    nationalities USING (nationality_id)
GROUP BY nationality
ORDER BY branda DESC
LIMIT 20;

I tried using:

COUNT((CASE brand WHEN 'brandb' OR 'brandb' THEN txn_id ELSE NULL END)) AS combined- however, this obviously returns too much (returns branda or brandb regardless of whether they were purchased together). I know that I can’t use AND, because, obviously, no cell will become the brand AND brandb.

I also tried using: COUNT((CASE brand WHEN IN('branda', 'brandb') THEN txn_id ELSE NULL END)) AS combined- However, this is not valid syntax.

, HAVING, , .

Answer 1

, :

SELECT n.nationality,
       sum(branda), sum(brandb), sum(branda * brandb)
FROM (SELECT t.txn_id, n.nationality,
             MAX(CASE brand WHEN 'branda' THEN 1 ELSE 0 END) AS branda,
             MAX(CASE brand WHEN 'brandb' THEN 1 ELSE 0 END) AS brandb
      FROM transaction_data t INNER JOIN
           products p
           USING (sku) INNER JOIN
           nationalities n
           USING (nationality_id)
      GROUP BY t.txn_id, n.nationality
     ) tn
GROUP BY n.nationality
ORDER BY max(txn_id) DESC
LIMIT 20;