Counts totaling up 3 fields by groups

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 totaling up 3 fields by groups

Author

Topic

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-12-23 : 18:06:04

I have a table that contains 3 INT fields, dt_portfolio1_ID, dt_portfolio2_ID, and dt_portfolio3_ID

Table description:


Image_ID int
dt_portfolio1_id int
dt_portfolio2_id int
dt_portfolio3_id int
Image_Active bit
User_ID int

I would like a total count of how many instances each particular portfolio ID# appears in all records. I would also like it to only count as 1 if the same porfolio ID # number is duplicated in more then 1 of the dt_portfolioN_ID fields in the same record.

What I have below compresses it down to 2 fields, a portfolio_ID and a count but the same porfolio # appears 3 times as it may be contained in any of the portfolio_id fields in each record.


SELECT Count(*) AS Portfolio_Counts, dt_Portfolio1_ID AS Portfolio_ID
 FROM dbo.UF2_rt_images 
 WHERE ((image_active = 1) AND (User_ID = 82)) 
 GROUP BY dt_Portfolio1_ID
 UNION ALL
SELECT Count(*) AS Portfolio_Counts, dt_Portfolio2_ID AS Portfolio_ID
 FROM dbo.UF2_rt_images 
 WHERE ((image_active = 1) AND (User_ID = 82)) 
 GROUP BY dt_Portfolio2_ID
 UNION ALL
SELECT Count(*) AS Portfolio_Counts, dt_Portfolio3_ID AS Portfolio_ID
 FROM dbo.UF2_rt_images 
 WHERE ((image_active = 1) AND (User_ID = 82)) 
 GROUP BY dt_Portfolio3_ID

This is the data that is returned


Count   Portfolio_ID
42 	NULL
142 	0
3 	1164
3 	18991
46 	NULL
142 	0
1 	18991
1 	24019
46	NULL
142	0
1	1164
1	24019

Desired result:


Count   Portfolio_ID
4	1164
4 	18991
2 	24019

I would like to ignore the nulls and O's combine the counts where the portfolio_ID is the same.

Thanks

Phil

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-23 : 18:58:41

Consider reading up on normalizaton and properly normalizing your table; then problems like this will not happen.

What does "is active" and "user ID" refer to? The image itself, or the the relation of the image with the portfolio(s)? WHICH portfolios?

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-12-23 : 19:18:35

The image_ID is just the record ID # PK, If the image_active bit is set then this record is to be included as seen in the WHERE clause along with the user_ID.

We actually had to denormalize the table a little bit to obtain better perfomance, this was one of the items that was denormalized. 1 write to the db for 3 portfolios instead of 3 writes to a portfolio table, and a few other speed increases based on queries for portfolios directly against the image table instead of a join with the portfolio table. We tried it both ways and this one won out speed wise.

So back to the question, is there a clean way to do what I ask above?

Thanks,

Phil

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-24 : 10:17:31

Though denormalizing the table helped your performance for one aspect, I believe it was not the way to achieve the improvement. Now all data retrieval operations will be difficult and slower than it needs to be. But to answer your question, this is the best I could come up with:


set nocount on
use pubs
go
create table dbo.UF2_rt_images 
	(Image_ID int
	,dt_portfolio1_id int
	,dt_portfolio2_id int
	,dt_portfolio3_id int
	,Image_Active bit
	,User_ID int)
go
insert dbo.UF2_rt_images 
select 1,1,1,3,1,82 union all
select 2,1,2,3,1,82 union all
select 3,1,2,3,1,82 union all
select 4,3,3,3,1,82 union all
select 5,1,2,3,1,82 union all
select 6,1,2,3,1,82 union all
select 7,1,2,3,1,82 union all
select 8,1,2,3,1,82 union all
select 9,1,2,3,1,82

go

select	count(*) [count]
	,b.portfolio_id 
from	(
	select	image_id, portfolio_id
	from	(--normalize the columns
		select	image_id, dt_portfolio1_id portfolio_id 
		from dbo.UF2_rt_images WHERE image_active = 1 AND User_ID = 82 
		union all
		select	image_id, dt_portfolio2_id portfolio_id
		from dbo.UF2_rt_images WHERE image_active = 1 AND User_ID = 82  
		union all
		select	image_id, dt_portfolio3_id portfolio_id 
		from dbo.UF2_rt_images WHERE image_active = 1 AND User_ID = 82 
		) a
	--distinct the list so as to not double count portfolio_ids for same image_id
	group by image_id, portfolio_id
	) b
group by b.portfolio_id

go
drop table dbo.UF2_rt_images

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-24 : 13:24:27

I would be interested in seeing the before and after SQL statements and DDL's for the tables to see why you feel that denormalizing gave you better performance. Definitely in this case the performance is much worse, and for any situation in which you need to search for a certain portfolio_id in this table (you have to search all 3 columns in your denormalized schema). Not only that, but manipulation of this table now is much, much more complicated since, again, 3 columns need to be checked in all situations instead of with a properly normalized design and if you need a 4th portfolio, how do you handle that?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources