help with query - SUM from table

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)

help with query - SUM from table

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-05 : 01:44:28

Hi,

I have the following query, which executes fine, but it's not bringing me back the value I want.

What I want specifically is the SUM of the "cityPage" columns.

Having some problems putting this together..

any help is greatly appreciated,

thanks once again! =]
mike123

SELECT city,count(*) as totalMembers, count(*) / 20 as cityPages from tbluserdetails

WHERE city IS NOT NULL AND city NOT IN ('','-') AND active = 1

GROUP BY city

HAVING count(*) > 50

ORDER BY totalMembers DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-05 : 02:10:45

Try adding "WITH ROLLUP" between GROUP BY and HAVING.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-05 : 02:11:32

Or make your current query as a dervied table and do the sum on that.

Or post some sample data and provide some expected output.

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-05 : 02:21:02

Hey Peso,

WITH ROLLUP looks like a great feature, I've never used it before. It looks like its grabbing the total sum of the column, regardless of my TOP statement so won't work in this particular situation, but I am already excited to add it to some other queries I have.

I think what I want to do is the derived table solution, but I can't figure out the syntax.

Here is some sample data:

Toronto 7369 368
London 7124 356
vancouver 4376 218
Winnipeg 4103 205
Calgary 3556 177
New York 3105 155

I want the single value result to be 1479 -> (368+356+218+205+17+155)

thanks again!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-05 : 05:09:52

How do you want the sum to be returned?
As a part of the resultset or in a variable?

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-05 : 05:20:23

syntax can be like this,

select 
	cola,
	sum(colc) 
from
	(select top 4 * from yourtable) s
group by 
	cola
with rollup

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-05 : 05:28:44

quote:
Originally posted by Peso

How do you want the sum to be returned?
As a part of the resultset or in a variable?

E 12°55'05.63"
N 56°04'39.26"

I just need an INTEGER returned from the function only . .could be via output param, or just 1 row / 1 column dataset

thx!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-05 : 06:08:07

Btw, the formala for calculating the CityPages needs a re-touch due to your integer division.

SELECT		City,
		COUNT(*) AS TotalMembers,
		CASE
			WHEN COUNT(*) % 20 = 0 THEN COUNT(*) / 20
			ELSE 1 + COUNT(*) / 20
		END AS CityPages
FROM		tblUserDetails
WHERE		City NOT IN ('', '-')
		AND Active = 1
GROUP BY	City
HAVING		COUNT(*) > 50
ORDER BY	COUNT(*) DESC

And with that in place, we can easily write a function for this.

CREATE FUNCTION dbo.fnGetCityPages
(
)
RETURNS INT
AS
BEGIN
	RETURN	(
			SELECT	SUM(CityPages)
			FROM	(
					SELECT TOP 50	CASE
								WHEN COUNT(*) % 20 = 0 THEN COUNT(*) / 20
								ELSE 1 + COUNT(*) / 20
							END AS CityPages
					FROM		tblUserDetails
					WHERE		City NOT IN ('', '-')
							AND Active = 1
					GROUP BY	City
					HAVING		COUNT(*) > 50
					ORDER BY	COUNT(*) DESC
				) AS d
		)
END

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources