sql join 문 해결할 쿼리
join 문으로 같은 본부 모델 타입 count 합쳐지게 해서 출력하기
미션 !
두 테이블 같은 본부 같은 모델타입은 count 개수 합치기
밑에는 gpt가 알려줬는데 너무 길다 ...
해결 쿼리는 ??
SELECT Place AS '본부', MODELTYPE, SUM(건수) AS count FROM (SELECT m.Place, m.MODELTYPE, COUNT(Place) AS '건수'
FROM cert_target ct
JOIN modem_info m ON ct.SN = m.serialNum
WHERE modeltype LIKE 'GAI-L52A-%'
GROUP BY m.Place, m.modeltype
UNION
SELECT m.Place, m.MODELTYPE, COUNT(Place) AS '건수'
FROM cert_target_ic ct
JOIN modem_info m ON ct.SN = m.serialNum
WHERE modeltype LIKE 'GAI-L52A-%' GROUP BY m.Place, m.modeltype ) AS sum GROUP BY Place, MODELTYPE;
join문 union
SELECT Place AS '본부', MODELTYPE, SUM(건수) AS count FROM (
SELECT mt.Place as '본부', mt.MODELTYPE,count(mt.Place) as '건수'
FROM cert_history ch
INNER JOIN modem_info mt
ON ch.ctn = mt.PhoneNum
INNER JOIN cert_target ct
ON mt.SerialNum = ct.SN
where ch.cert_flag = 1 and modeltype like 'GAI-L52A-%' group by mt.Place, mt.modeltype
union
SELECT mt.Place as '본부', MODELTYPE,count(Place) as '건수'
FROM cert_history ch
INNER JOIN modem_info mt
ON ch.ctn = mt.PhoneNum
INNER JOIN cert_target_ic ti
ON mt.SerialNum = ti.SN
where ch.cert_flag = 1 and modeltype like 'GAI-L52A-%' group by mt.Place, modeltype
) as sum group by mt.place, modeltype;
쿼리 1
SELECT Place as '본부', MODELTYPE,count(Place) as count
FROM cert_history ch
INNER JOIN modem_info mt
ON ch.ctn = mt.PhoneNum
INNER JOIN cert_target ct
ON mt.SerialNum = ct.SN
where ch.cert_flag = 1 and modeltype like 'GAI-L52A-%' group by mt.Place, modeltype;
쿼리2
SELECT Place as '본부', MODELTYPE,count(Place) as count
FROM cert_history ch
INNER JOIN modem_info mt
ON ch.ctn = mt.PhoneNum
INNER JOIN cert_target_ic ti
ON mt.SerialNum = ti.SN
where ch.cert_flag = 1 and modeltype like 'GAI-L52A-%' group by mt.Place, modeltype;
를 합한 쿼리인데 왜
ERROR 1054 (42S22): Unknown column 'Place' in 'field list' 이 에러가 계속 나냔말요