How do I get the score of each individual value in multiple columns and get the result in separate columns?
I need the following table for a query to get the result of the below table.
Table:
----------------------------------
| Name | Age | slot |
|-------|--------|---------------|
|A |20 | 1 |
|B |30 | 2 |
|C |30 | 1 |
|D |20 | 1 |
|E |40 | 2 |
|F |40 | 3 |
|G |50 | 3 |
----------------------------------
Result:
-------------------------------------------
|Age |Age_Count |Slot |Slot_Count|
-------------------------------------------
|20 | 2 |1 |3 |
-------------------------------------------
|30 | 2 |2 |2 |
-------------------------------------------
|40 | 2 |3 |2 |
-------------------------------------------
|50 | 1 |
-----------------------
While searching stackoverflow I found this question for a single column and there is [this link for multiple columns] ( get count of each individual value in a Multiple Columns column). The responses from the second link (for multiple calls) are displayed under one column, and my requirement is perhaps quite different from the responses posted there.
Thank you in advance
0
source to share
1 answer
Your request is odd. Are you sure you want this?
If so, this might help:
SET @x:=0,@y:=0,@m:=0,@n:=0;
SELECT
DISTINCT age,age_count, slot,slot_count
FROM (
SELECT
age, age_count, slot, slot_count
FROM (
SELECT
@x:=@x + 1 AS aid, age, COUNT(*) age_count
FROM
slots
GROUP BY age
) a
LEFT JOIN (
SELECT
@y:=@y + 1 AS sid, slot, COUNT(*) slot_count
FROM
slots
GROUP BY slot
) s ON a.aid = s.sid
UNION
SELECT
age, age_count, slot, slot_count
FROM (
SELECT
@m:=@m + 1 AS aid, slot, COUNT(*) slot_count
FROM
slots
GROUP BY slot
) a
LEFT JOIN (
SELECT
@n:=@n + 1 AS sid, age, COUNT(*) age_count
FROM
slots
GROUP BY age
) s ON a.aid = s.sid
) a
If you know for sure that you have more unique ages than unique slots, or vice versa, you can enjoy a hectic alliance.
0
source to share