Creating grouped SQL output with mixed data types

Data and Databases
1

I need help creating a SQL query that produces a specific output format. I’m trying to combine data from multiple tables but UNION doesn’t seem to work the way I need it to.

Here’s what I want my final result to look like:

name  | count | desc1 | desc2 
------------------------------
  X   |  15   |       |
      |   4   | item1 | data1  
      |   7   | item2 | data2
      |   6   | item3 | data3
  Y   |  12   |       |
      |   8   | item4 | data4  
      |   9   | item5 | data5
      |   1   | item6 | data6

I tried this approach:

SELECT p.category, NULL as amount, p.description FROM products p 
WHERE p.category = 'X' 
UNION 
SELECT NULL as category, o.quantity, o.notes FROM orders o 
WHERE o.product_type = 'X';

But it only returns one record instead of the grouped format I need. How can I modify my query to get this kind of hierarchical output where some rows show the main category and others show the detail records underneath?

2

hmm interesting challenge! what database system are you working with exactly? some have better support for this hierarchical stuff than others. also curious - are those count values (15, 4, 7, etc) supposed to be calculated totals or do they come from seperate fields? that might change the aproach significantly

3

your union query needs an ORDER BY clause to group things properly. try adding a sorting column like SELECT p.category, 0 as sort_order, NULL as amount for headers and SELECT p.category, 1 as sort_order, o.quantity for details, then ORDER BY category, sort_order. thats how i usually handle this kinda hierarchical stuff in sql

4

The issue stems from your UNION structure not including all necessary columns and lacking proper grouping logic. You need to ensure both SELECT statements have identical column structures and add a mechanism to distinguish between header and detail rows. Consider using a UNION ALL approach where you first select category totals as header rows, then select detail records with empty category fields. Add a calculated field to control sorting - something like CASE WHEN category IS NOT NULL THEN 0 ELSE 1 END as row_type. This ensures headers appear before their corresponding details. Alternatively, investigate using window functions with ROLLUP or GROUPING SETS if your database supports them. These can generate the hierarchical structure more elegantly than multiple UNION operations. The key is maintaining consistent column types across all parts of your query while controlling the display order through strategic sorting.