How to get multiple counts for different fields

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)

How to get multiple counts for different fields

Author

Topic

Leddo
Starting Member

6 Posts

Posted - 2006-07-11 : 00:45:24

I'm not sure how to ask this properly, but here goes...

I have a table - Houses - which looks like this...


PlanID	Bedrooms  Levels  Bathrooms  Carspaces  Width  Region      AgentName
2       3         1       1          2          14000  Brisbane    Agent A
3       3         1       1          2          14000  Gold Coast  Agent B
4       4         1       1          2          14500  Brisbane    Agent A
5       4         1       1          2          15000  Brisbane    Agent C
6       4         2       2          2          11700  Brisbane    Agent A
7       4         2       2          2          11700  Gold Coast  Agent B

What I would like to do is to have a query that returns the summary of grouping values, and the count of the records for each one. eg

Should return something like...


grpField   grpValue   grpCount
Bedrooms   3          2
Bedrooms   4          4
Levels     1          4
Levels     2          2
Carspaces  2          6
Region     Brisbane   4
Region     Gold Coast 2
Agent      Agent A    3
Agent      Agent B    2
Agent      Agent C    1

The columns I want to group by are not all the available columns. If I can manage to crack this, the next step will be to also do a range. eg.
Also returns....


Width  11000 - 11999   2
Width  14000 - 14999   3
Width  15000 - 15999   1

I have tried doing UNIONs for each SELECT statement with a Group By for each field, but as you can imagine the code doesn't perform very effeciently. I'm thinking there must be a better way.

Thanks for your help.

Chris

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2006-07-11 : 00:55:16

> I'm thinking there must be a better way.

I doubt it.
Plus what does "the code doesn't perform very effeciently" mean? Perform not instantly?

======================
Let me hug & kiss you...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-11 : 01:31:44

insert into a temp table then do the select instead of the union?

you can compare the execution plan to see if this will make any difference at all

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-11 : 01:46:48

This code should give you an idea how to solve your problem

-- prepare test data
declare	@houses table
	(
		PlanID tinyint,
		Bedrooms tinyint,
		Levels tinyint,
		Bathrooms tinyint,
		Carspaces tinyint,
		Width smallint,
		Region varchar(10),
		AgentName varchar(7)
	)

insert	@houses
select	2, 3, 1, 1, 2, 14000, 'Brisbane', 'Agent A' union all
select	3, 3, 1, 1, 2, 14000, 'Gold Coast', 'Agent B' union all
select	4, 4, 1, 1, 2, 14500, 'Brisbane', 'Agent A' union all
select	5, 4, 1, 1, 2, 15000, 'Brisbane', 'Agent C' union all
select	6, 4, 2, 2, 2, 11700, 'Brisbane', 'Agent A' union all
select	7, 4, 2, 2, 2, 11700, 'Gold Coast', 'Agent B'

-- Do the dirty work
DECLARE	@Output TABLE
	(
		grpOrder tinyint,
		grpField VARCHAR(10),
		grpValue VARCHAR(13),
		grpCount INT
	)

INSERT		@Output
SELECT		1,
		'Bedrooms',
		Bedrooms,
		COUNT(*)
FROM		@houses
GROUP BY	Bedrooms

INSERT		@Output
SELECT		2,
		'Levels',
		Levels,
		COUNT(*)
FROM		@houses
GROUP BY	Levels

INSERT		@Output
SELECT		3,
		'Carspaces',
		Carspaces,
		COUNT(*)
FROM		@houses
GROUP BY	Carspaces

INSERT		@Output
SELECT		4,
		'Region',
		Region,
		COUNT(*)
FROM		@houses
GROUP BY	Region

INSERT		@Output
SELECT		5,
		'Agent',
		AgentName,
		COUNT(*)
FROM		@houses
GROUP BY	AgentName

INSERT		@Output
SELECT		6,
		'Width' grpField,
		Range grpValue,
		COUNT(*) grpCount
FROM		(
			SELECT	CASE
					WHEN Width BETWEEN     0 AND 10999 THEN '    0 - 10999'
					WHEN Width BETWEEN 11000 AND 11999 THEN '11000 - 11999'
					WHEN Width BETWEEN 12000 AND 12999 THEN '12000 - 12999'
					WHEN Width BETWEEN 13000 AND 13999 THEN '13000 - 13999'
					WHEN Width BETWEEN 14000 AND 14999 THEN '14000 - 14999'
					WHEN Width BETWEEN 15000 AND 15999 THEN '15000 - 15999'
					WHEN Width BETWEEN 16000 AND 99999 THEN '16000 - 99999'
				END Range
			FROM	@houses
		) z
GROUP BY	Range

SELECT		grpField,
		grpValue,
		grpCount
FROM		@Output
ORDER BY	grpOrder,
		grpValue


grpField   grpValue       grpCount
---------  -------------  --------
Bedrooms               3         2
Bedrooms               4         4
Levels                 1         4
Levels                 2         2
Carspaces              2         6
Region     Brisbane              4
Region	   Gold Coast            2
Agent      Agent A               3
Agent      Agent B               2
Agent      Agent C               1
Width      11000 - 11999         2
Width      14000 - 14999         3
Width      15000 - 15999         1

You should consider normalizing your data.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-11 : 02:02:23

In my code above (dynamic ranging), you can change the width intervals for grpOrder 6 to whatever you want. That is more usable.
But grpOrder 6 can be substituted with this code, if you know you always want the widths in interval of 1,000's (static ranging).

INSERT		@Output
SELECT		6,
		'Width' grpField,
		Range grpValue,
		COUNT(*) grpCount
FROM		(
			SELECT	CONVERT(VARCHAR, Width - Width % 1000) + ' - ' + CONVERT(VARCHAR, 999 + Width - Width % 1000) Range
			FROM	@houses
		) z
GROUP BY	Range

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-11 : 06:56:12

I think I would be tempted into creating a table function to transpose the data and make it easier to work with (for this purpose, at least), and then I'd do the grouping on that. I think there could be a trade-off between efficiency and easy of (re-)use, and what's best for you will be down to your situation.

I've nabbed some of Peso's ideas, so it's not that far removed from it...

--data / objects
if object_id('houses') is not null drop table houses
create table houses
	(
		PlanID tinyint,
		Bedrooms tinyint,
		Levels tinyint,
		Bathrooms tinyint,
		Carspaces tinyint,
		Width smallint,
		Region varchar(10),
		AgentName varchar(7)
	)

insert	houses
select	2, 3, 1, 1, 2, 14000, 'Brisbane', 'Agent A' union all
select	3, 3, 1, 1, 2, 14000, 'Gold Coast', 'Agent B' union all
select	4, 4, 1, 1, 2, 14500, 'Brisbane', 'Agent A' union all
select	5, 4, 1, 1, 2, 15000, 'Brisbane', 'Agent C' union all
select	6, 4, 2, 2, 2, 11700, 'Brisbane', 'Agent A' union all
select	7, 4, 2, 2, 2, 11700, 'Gold Coast', 'Agent B'

go
if object_id('fnHouseAttributes') is not null drop function fnHouseAttributes
go
create function dbo.fnHouseAttributes()
returns @t table (
    PlanId tinyint,
    Attribute varchar(20),
    AttributeValueGroup as
        case
            when Attribute = 'Width' then
                cast(
                    CONVERT(VARCHAR, AttributeValue - AttributeValue % 1000) + ' - ' + 
                    CONVERT(VARCHAR, 999 + AttributeValue - AttributeValue % 1000)
                as varchar(20))
            else AttributeValue
        end,
    AttributeValue varchar(20))
as
begin
insert @t (PlanId, Attribute, AttributeValue)
          select PlanId, 'Bedrooms', Bedrooms from houses
union all select PlanId, 'Levels', Levels from houses
union all select PlanId, 'Bathrooms', Bathrooms from houses
union all select PlanId, 'Carspaces', Carspaces from houses
union all select PlanId, 'Width', Width from houses
union all select PlanId, 'Region', Region from houses
union all select PlanId, 'AgentName', AgentName from houses

return
end
go

--calculation
select
    Attribute as grpField,
    AttributeValueGroup as grpValue,
    count(*) as grpCount
from dbo.fnHouseAttributes()
group by Attribute, AttributeValueGroup
order by Attribute, AttributeValueGroup

--tidy up
if object_id('houses') is not null drop table houses
if object_id('fnHouseAttributes') is not null drop function fnHouseAttributes

/*results
grpField             grpValue             grpCount    
-------------------- -------------------- ----------- 
AgentName            Agent A              3
AgentName            Agent B              2
AgentName            Agent C              1
Bathrooms            1                    4
Bathrooms            2                    2
Bedrooms             3                    2
Bedrooms             4                    4
Carspaces            2                    6
Levels               1                    4
Levels               2                    2
Region               Brisbane             4
Region               Gold Coast           2
Width                11000 - 11999        2
Width                14000 - 14999        3
Width                15000 - 15999        1
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Leddo
Starting Member

6 Posts

Posted - 2006-07-11 : 15:04:52

Thanks everyone - what awesome responses...

The thing I'm still concerned about is that if I have to do this everytime a search is performed from a website, then I need it to be the quickest...

for instance, have a look at: [url]http://www.dell.com/content/products/category.aspx/notebooks?c=us&l=en&s=bsd&cs=04&~ck=bt[/url]

When you do a search, on the left hand side, it summarises the results you have. If you click on one of the summary fields, then it adds that to the search.

I'm trying to implement something similar to our property website, so the table "Houses" will actually end up also having lots of where clauses in it as well. eg.

SELECT .... FROM Houses WHERE Bedrooms=4 and Bathrooms=2

And the more they "refine the search" the longer that where clause will be. I gather I could just reproduce the where clause for each "SELECT From Houses" statement.. Or am I best to create a temporary table with the search results, then use that to do the grouping?

Thanks again for your help.

Chris

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-11 : 15:31:13

For the width groups, I highly recommend creating a simple "WidthGroups" table with min/max values per group and join to that. Much easier to maintain and shorter and more efficient than hard-coding values into a SELECT statement.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-11 : 15:51:31

Also, if there is no much activity on the houses table, add a trigger to do the job [described above] for you and store the result in an auxiliary table named websearch or something. Then always search the websearch table instead of the houses table.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources