[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..."
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.
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
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
select type as 1,count(*)as count 7 from table where category like '%full size 6 - pickup%'
select type as 2,count(*) as 5 count from table where category like '%truck%'
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
More Related questions