Search code examples
sqlsubquery

Insert into Multiple Columns from a Grouped by Subquery

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

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

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