Category Totals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Category Totals

Author

Topic

LeeNewman
Starting Member

16 Posts

Posted - 2009-02-26 : 07:13:32

Hi people

I am posting here mainly because I require the answer to a SQL query that is just simply beyond my ability. I have spent months on and off looking at this problem but as yet, have been unable to come up with a solution. So now I figured that maybe, just maybe, someone here may have done something similar in the past or is just plain clever!

I have two tables, one called tbl_dir_details and another called tbl_dir_categories.
tbl_dir_details has 3 columns in (amongst others) called Category, Category2 and Category3.
tbl_dir_categories has a column in it (amongst others) called ID.
The numbers in tbl_dir_details.Category, tbl_dir_details.Category2 and tbl_dir_details.Category3 are representative of the numbers in tbl_dir_categories.ID. So each record in tbl_dir_details has 3 categories assigned to it.

What I would like to establish is how many records in tbl_dir_details are a member of each category tbl_dir_categories.ID).

For example, lets take the first category with a ID number of 1. I need to find the number of occurrences of the number 1 in tbl_dir_details.Category, tbl_dir_details.Category2 and tbl_dir_details.Category3. But! I need the query to take
the category ID from tbl_dir_categories.ID rather than me manually specifying it in the SQL query as there are 1500 categories.

Ideally, I would like a count as total column added to tbl_dir_categories so my application can then reference the total for each category.

Does all that makes sense??

If anyone one of your gurus could help me to solve this problem, It would be so very much appreciated!

Many Thanks if you've read this far.

Lee

bjoerns
Posting Yak Master

154 Posts

Posted - 2009-02-26 : 07:43:42

[code]declare @category table (i int)
insert into @category
select 1 union all
select 2 union all
select 3 union all
select 4

declare @details table (cat1 int, cat2 int, cat3 int)
insert into @details
select 1, 3, 4 union all
select 0, 0, 2 union all
select 1, 1, 1 union all
select 4, NULL, 5

select c.i,
sum(case when d.cat1 = c.i then 1 else 0 end) +
sum(case when d.cat2 = c.i then 1 else 0 end) +
sum(case when d.cat3 = c.i then 1 else 0 end)
from @details d
cross join @category c
group by c.i

output:

1 4
2 1
3 1
4 2[/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-26 : 07:49:19

Or this,


select 
	col,count(id) 
from
	(
	select category col from tbl_dir_details
	union all
	select category1 from tbl_dir_details
	union all
	select category2 from tbl_dir_details
	)abc 
	full join tbl_dir_categories on id=col
group by
	col

LeeNewman
Starting Member

16 Posts

Posted - 2009-02-26 : 14:28:17

Thanks guys!

bjoerns - Sorry, I didn't really understand your post. I didn't understand what you were doing or even the results that came back. But I do appreciate you giving me your time! As I said in my original post...i'm no expert so please do not take offence.

sakets - Your option worked perfectly for me and I understood what was happening.

Thank you to both of you for all your help! You won't believe how much effort it's taken me just to get this far. I've still got some work to do before I can integrate category totals into the directory itself as it's a very complicated setup but maybe i'll bug you both about that another day :o)

Take care!

Lee

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-26 : 23:28:08

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources