Counts combining multiple columns

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 2000 Forums

Transact-SQL (2000)

Counts combining multiple columns

Author

Topic

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-06-22 : 05:07:55

I have a table used for documenting images that contains 3 Fields, Cat1_ID, Cat2_ID, Cat3_ID which are type INT

Each of these fields can contain a category number of which right now there are approximately 30 categories. Any of the 30 category IDs can be entered in any or all of these fields or they can be set NULL. The WHERE clause would be WHERE User_ID = xxx as each image belongs to a user. The idea being to display how many images a user has in each of the categories.

What I need is to count how many of each category (1-30) there are in all 3 of the fields, don't care which field they are in, just want a grand total for each of the 30 categories. I would like it to only count as one though if the same category is duplicated in 2 or more fields.

I have looked through the forums and BOL and I see things that are close to what I want but I am still not sure how it is done. The table contains around 5,000,000 records so i need to have a very efficient count. Once the count is derived I will need to figure some better way to save it as a derrived table so that I don't have to go through this count procedure each time the page is displayed. Only when an image is added, deleted or modified.

Any Help would be appreciated.

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-22 : 05:14:26

apply this to your question
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
and we can help you faster.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-22 : 05:15:04

Post table structure with sample data and expected outcome

Madhivanan

Failing to plan is Planning to fail

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-06-22 : 05:34:58

OK, Here is the table structure

Image_ID, User_ID, Category1_ID, Category2_ID, Category3_ID

1 82 12 10 1
2 82 12 15 25
3 82 15 1 18
4 82 12 12 12 ' only counts as 1 entry for category 12 even though it is there 3 times

Results for user 82
----------------------
Category 1 = 2
Category 10 = 1
Category 12 = 3
Category 15 = 2
Category 18 = 1
Category 25 = 1

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-22 : 06:04:08

Something like this but not sure whether this works for your actual data

declare @t table(i int, cat1 int, cat2 int , cat3 int)
insert into @t values(82,12, 10 ,1)
insert into @t values(82,12, 15 ,25)
insert into @t values(82,15 ,1 ,18)
insert into @t values(82,12,12 ,12)

select i,category,count(counting) counts from(
Select distinct i,cat1 as Category,(select count(cat1) from @t 
where i=T.i and cat1=T.cat1) as counting from @t T
Union all
Select distinct i,cat2,(select count(cat2) from @t 
where i=T.i and cat2=T.cat2) from @t T
Union all
Select distinct i,cat3,(select distinct count(cat3) from @t 
where i=T.i and cat3=T.cat3) from @t T
) T group by i,category

Madhivanan

Failing to plan is Planning to fail

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-06-23 : 15:10:31

Madhivanan,

Thank you for the query but I am still having a bit of a problem with it. I converted what you showed to the following representing the actual table fields and table name. It almost works.
-----------------------------------------------------
Translation of original values from your sample:

T = UF_rt_Images
i = UF_rt_Images.user_id
cat1 = UF_rt_Images.dt_category1_ID
cat2 = UF_rt_Images.dt_category2_ID
cat3 = UF_rt_Images.dt_category3_ID

-------------------------------------------

select UF_rt_Images.user_ID,category,count(counting) counts from(
Select distinct UF_rt_Images.User_ID,dt_category1_ID as Category,(select count(dt_category1_ID) from UF_rt_Images
where UF_rt_Images.user_id=82 and UF_rt_Images.dt_category1_ID=dt_category1_ID) as counting from UF_rt_Images
Union all
Select distinct UF_rt_Images.User_ID,dt_category2_ID,(select count(dt_category2_ID) from UF_rt_Images
where UF_rt_Images.User_ID=82 and UF_rt_Images.dt_category2_ID=dt_category2_ID) from UF_rt_Images
Union all
Select distinct UF_rt_Images.user_id,dt_category3_ID,(select distinct count(dt_category3_ID) from UF_rt_Images
where UF_rt_Images.user_ID=82 and UF_rt_Images.dt_category3_ID =dt_category3_ID ) from UF_rt_Images) UF_rt_Images where UF_rt_Images.user_ID=82 group by User_ID,category

I had to add "where UF_rt_Images.user_ID=82" to the very end of the query so it would return only info for user 82 instead of all users.

Here are the results which are not correct, but it is getting close. Have I missed something? A little too overzealous in changing out field names instead of leaving them as variables?

82 NULL 3
82 0 3
82 1 1
82 2 1
82 3 1
82 4 1
82 6 1
82 7 1
82 8 1
82 11 1
82 12 1
82 15 1
82 16 1
82 17 2
82 20 2
82 24 3
82 28 1
82 30 1
82 31 1
82 33 3

Thanks,

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-24 : 01:16:05

Can you post some actual data you used?

Madhivanan

Failing to plan is Planning to fail

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-06-24 : 02:51:08

OK, Here is the query of all of the images for user 82. I deleted the entries that had nothing in the category fields, those are all set to either a 0 or null of which there were a few hundred.

SELECT Image_ID, User_ID, dt_category1_ID, dt_Category2_ID, dt_category3_ID FROM UF_rt_Images WHERE user_ID = 82 ORDER BY Image_ID ASC

Results
image_ID, user_id, dt_category1_ID, dt_category2_ID, dt_category3_ID
11231 82 20 0 0
11648 82 20 0 0
29661 82 15 0 0
100126 82 8 0 0
100129 82 8 0 0
100210 82 8 0 0
101322 82 8 0 0
106573 82 17 0 0
118468 82 11 0 0
118469 82 11 0 0
118479 82 6 0 0
118485 82 24 0 0
121319 82 12 0 0
121350 82 12 0 0
121373 82 11 0 0
122555 82 1 0 0
122561 82 12 0 0
123023 82 16 0 0
125800 82 6 0 0
128576 82 16 0 0
158049 82 15 0 0
159393 82 6 0 0
166443 82 15 0 0
181622 82 8 0 0
181633 82 8 0 0
181647 82 8 0 0
188402 82 24 0 0
215084 82 24 0 0
228316 82 15 0 0
228320 82 15 0 0
228804 82 15 0 0
228811 82 15 0 0
402255 82 1 0 0
513676 82 7 17 30
515839 82 7 20 31
515843 82 7 20 31
515845 82 7 20 31
1000006 82 1 24 33
1000008 82 1 24 33
1000010 82 28 24 33
1000011 82 28 24 33
1000017 82 2 33 24
1000020 82 2 33 24
1000021 82 2 NULL NULL
1000022 82 2 NULL NULL
1000026 82 33 NULL NULL
1000027 82 33 4 3
1000033 82 6 NULL NULL

This is the actual data in the table.

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-06-26 : 15:05:34

Did everyone give up on this one? Still could use the help.

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources