Advanced SQL GROUP BY query

Question

I have two columns in my table "Category", "Category2", two columns contain essentially the same information. If I created a database, I would create a separate table for categories and added items in categories based on this table, unfortunately, I did not create a database, and I can’t change it now, but I think there is still way to do what i want to do.

Below is an example table

Category             Category2
------------------   -----------------
truck                full size - pickup
full size - pickup   truck
Sedan                Import - Sedan
Convertible          Domestic - Coupe

I want to run a query to calculate the total number of trucks, sedans, full size pickup, etc. I tried the following query, but it grouped two columns separately

SELECT Category, Count(*) as Count
FROM Items
GROUP BY Category, Category2

Answer 1

.

SELECT Category, Count(*) as TheCount
FROM
(
  SELECT Category1 as Category
  FROM Items
  UNION ALL
  SELECT Category2
  FROM Items
) sub
GROUP BY Category

Answer 2

, category2 ( "", "2" ), , . :

SELECT items.category /* , other columns... */
FROM items
UNION ALL
SELECT items.category2 /* , other columns... */
FROM items

, , , :

SELECT category, count(*) FROM (
    SELECT items.category FROM items
    UNION ALL
    SELECT items.category2 FROM items
    ) expanded
GROUP BY category

, , :

with subcounts as (
  select items.category, items.category2, count(*) as subcount
  from items
  group by category, category2)
select category, sum(subagg) as finalcount from (
  select subcounts.category, sum(subcount) as subagg from subcounts group by category
  union all
  select subcounts.category2, sum(subcount) as subagg from subcounts group by category2
) combination
group by category

, , . , "WITH..."

EDIT:

, , . , PostgreSQL:

SELECT category, count(*) FROM (
  SELECT CASE selector WHEN 1 THEN category WHEN 2 THEN category2 END AS category
  FROM Items, generate_series(1,2) selector
) items_fixed GROUP BY category

, postgresql, "generate_series (1,2)", "", : "1" "2". IMHO - postgresql. , , , , SQL Server. : "( 1 all select 2)". "( (1), (2)) ()", postgres-, . , , .

. "items_fixed" - , , , .

Answer 3

select category,sum(CategoryCount)
from(
select Category1 as category, count(Category1) as CategoryCount
from Table
group by Category1
union all
select Category2 as category, count(Category2) as CategoryCount
from Table
group by Category2) x
group by category

Answer 4

, ,

declare @group1 (Category1, Count int)
declare @group2 (Category2, Count int)

insert into @group1 (Category1, Count1)
select Category1, count(Category1)
from Table
group by Category1

insert into @group2 (Category2, Count2)
select Category2, count(Category2)
from Table
group by Category2

select 
coalesce(Category1, Category2) as Category,
coalesce(Count1,0) + coalesce(Count2,0) as CountAll
from @group1 a
    full outer join @group2 b
        on a.Category1=b.Category2

Answer 5

1, (*) , "% full size - pickup%"

2, (*) , "% truck%"

3, (*) , "% %"
  ..

1 2 ..

,