i have this kind of structure
kode data
+------------+-----------+
| code | ket |
+------------+-----------+
|20401 | code one |
|21401 | code two |
|22401 | code three |
|etc | etc |
+------------------------+
+-----+-----------------+-----------+
| id | code1 | code2 |
+-----+-----------------+-----------+
|1 | 20401 | 21402 |
|2 | 21401 | 22401 |
|3 | 22401 | 20401 |
|4 | 20401 | 21401 |
+-----------------------+-----------+
how tow count and sum to achieve output like below
+-----------+-------------+-------------+ |code | count code1 | count code2 | +-----------+-------------+-------------+ |code one | 2 | 1 | |code two | 1 | 1 | |code three | 3 | 1 | |etc | etc | etc | +-----------+-------------+-------------+
basicly what i want is select table ket and count in code1 and code2 that hace the code with one query.
You could perform two joins:
SELECT ket AS code,
cnt1 AS "count code1"
cnt2 AS "count code2"
FROM kode
LEFT JOIN (SELECT code1, COUNT(*) AS cnt1
FROM data
GROUP BY code1) c1 ON kode.code = c1.code1
LEFT JOIN (SELECT code2, COUNT(*) AS cnt2
FROM data
GROUP BY code2) c2 ON kode.code = c2.code2
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加