[ACCEPTED]-Advanced SQL GROUP BY query-sql-server

Just dump both categories into a single 1 column before grouping.

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

Imagine that a row with "category, category2" can 32 be transformed to two rows (one with "category", one 31 with "category2") to get what you want. You'd 30 do that like this:

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

So all you then need to 29 do is aggregate across these:

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

You can also 28 do the aggregate by stages like this if 27 your database supports it:

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

This will limit 26 to just one scan of the main items table, good 25 if you only have a small number of categories. You 24 can emulate the same thing with temp tables 23 in databases that don't support "WITH..."

EDIT:

I 22 was sure there had to be another way to 21 do it without scanning Items twice, and 20 there is. Well, this is the PostgreSQL version:

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

The 19 only postgresql-specific bit here is "generate_series(1,2)" which 18 produces a "table" containing two rows-- one 17 with "1" and one with "2". Which is IMHO 16 one of the handiest features in postgresql. You 15 can implement similar things in the like 14 of SQL Server as well, of course. Or you 13 could say "(select 1 as selector union all 12 select 2)". Another alternative is "(values(1),(2)) series(selector)" although 11 how much of that syntax is standard and 10 how much is postgres-specific, I'm not sure. Both 9 these approaches have an advantage of giving 8 the planner an idea that there will only 7 be two rows.

Cross-joining this series table 6 items allows us generate two output rows 5 for each row of item. You can even take 4 that "items_fixed" subquery and make it 3 a view -- which btw is the reverse of the 2 process I tend to use to try and solve these 1 kind of problems.

try

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

0

im sure there's a better way to do it, but 1 here ya go

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

try this

select type as 1,count(*)as count 7 from table where category like '%full size 6 - pickup%'

union

select type as 2,count(*) as 5 count from table where category like '%truck%'

union

select 4 type as 3,count(*) as count from table where 3 category like '%sedan%'
and so on......

type 2 1 will be your full-size count type 2 your 1 truck count and so on....

hope this helps