Insert into Multiple Columns from a Grouped by Subquery

February 15, 2022

INSERT INTO TABLE (COLUMN_A, COLUMN_B, COLUMN_C)              -- Line 1
SELECT 'Label A',                                             -- Line 2
(SELECT CATEGORY, COUNT(1) FROM SUB_TABLE GROUP BY CATEGORY); -- Line 3, need help

Result from Line 3

What I want to achieve

‘Label A’ to be inserted into COLUMN_A
Grouped by CATEGORY from Line 3 to be inserted into COLUMN_B
Grouped by COUNT(1) from Line 3 to be inserted into COLUMN_C

Expected Result

I encountered an error: "subquery must return only one column". How can I achieve the expected result?

>Solution :

try like below if want to use subsqery

INSERT INTO TABLE (COLUMN_A, COLUMN_B, COLUMN_C)              -
SELECT 'Label A', CATEGORY,  cnt from                                          
(SELECT CATEGORY, COUNT(1) as cnt FROM SUB_TABLE GROUP BY CATEGORY) a

in fact don’t need sub-query

 INSERT INTO TABLE (COLUMN_A, COLUMN_B, COLUMN_C)              -
SELECT 'Label A', CATEGORY,  COUNT(1) as cnt from                                          
  FROM SUB_TABLE GROUP BY CATEGORY

demo link