I has Join table, and result is like this
IDA IDB QTY A 3 1 A 4 1 A 5 1 B 3 1 B 4 1 C 3 2 D 3 2 E 3 1 F 4 1 G 3 1 G 4 1 G 5 1 H 3 3 H 4 3 H 5 3
i’m confused how to count the IDA who has same Condition of IDB and QTY together.
So what i want is:
Combination of IDB - QTY (3-1, 4-1, 5-1) = 2 from IDA (A and G) (3-1) = 1 from IDA (E) (3-2) = 2 from IDA (C and D)
So basically i want count of IDA who has same IDB and QTY Combination. What Query should i do? i still don’t get how making query for this logic, thank you.
Edit :
What i want is the result in one query, i will not making manually Where condition of IDB-QTY Combination. (so basically the result is not from Where condition)
Advertisement
Answer
You can use aggregating with using group_concat() function
select group_concat( ida order by ida ) as grouped_letters, count(*) as count
from
(
select ida, count( idb - qty ) as ct, sum( idb - qty ) as sm
from tab
group by ida
) q
group by ct, sm
order by ida;
+---------------+-----+
|grouped_letters|count|
+---------------+-----+
| B | 1 |
| C,D | 2 |
| E | 1 |
| F | 1 |
| A,G | 2 |
| H | 1 |
+---------------+-----+