SQL Server Performance Forum – Threads Archive

T-sql query

Hi, I have a table as shown below col1 col2 1 16
2 24
3 28
3 32
5 40
6 42 i need a query that will display sum of col1 in the rage 15-25,26-35,36-45 for examples 2,15-25 2,26-35 2,36-45 How can i write the query in sql server2000 Thanks in advance emamuthu

I’m totally lost. What is the SUM going to be of/for? Do you really mean you want a sum of col1, or did you mean a sum of Col2 for values in Col1? Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.

CREATE TABLE #TEMP (ID SMALLINT IDENTITY, DATA NUMERIC(2)) go INSERT INTO #TEMP VALUES (16); INSERT INTO #TEMP VALUES (24); INSERT INTO #TEMP VALUES (2<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />; INSERT INTO #TEMP VALUES (32); INSERT INTO #TEMP VALUES (40); INSERT INTO #TEMP VALUES (42); SELECT COUNT(*) AS INSTANCES, ’15-25′ AS DATA FROM #TEMP WHERE DATA BETWEEN 15 AND 24 UNION SELECT COUNT(*) AS INSTANCES, ’26-35′ AS DATA FROM #TEMP WHERE DATA BETWEEN 26 AND 35 UNION SELECT COUNT(*) AS INSTANCES, ’36-45′ AS DATA FROM #TEMP WHERE DATA BETWEEN 36 AND 45 ORDER BY DATA DROP TABLE #TEMP go - Tahsin

You may be referring to SUM OF COUNT but don’t know specifically how. Can you elucidate? May the Almighty God bless us all!
www.empoweredinformation.com

Look for cross-tab reports in sql server help file Madhivanan Failing to plan is Planning to fail

Typically one will want this to be displayed horizontically flattened like this:
CREATE TABLE t
(
col1 INT
, col2 INT
)
INSERT INTO t
SELECT 1, 16
UNION ALL SELECT 2, 24
UNION ALL SELECT 3, 28
UNION ALL SELECT 3, 32
UNION ALL SELECT 5, 40
UNION ALL SELECT 6, 42 SELECT
SUM(CASE WHEN col2 >= 15 AND col2 < = 25 THEN 1 ELSE 0 END) AS [15-25]
, COUNT(CASE WHEN col2 >= 26 AND col2 < = 35 THEN 1 ELSE NULL END) AS [26-35]
, COUNT(CASE WHEN col2 >= 36 AND col2 < = 45 THEN 1 ELSE NULL END) AS [36-45]
FROM t DROP TABLE t 15-25 26-35 36-45
———– ———– ———–
2 2 2 (1 row(s) affected) Warning: Null value is eliminated by an aggregate or other SET operation. Note that you can use either SUM or COUNT. However when you use COUNT, make sure to use NULL in the ELSE fork as COUNT is NULL-aware and yields a different (and wrong) result when you put 0 in there. But as Madhivanan already mentioned, such things are usually better done at the presentational layer. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Is this what you are looking for??? select ’15-25′ [Data Range], count(*) [Count] from #temp where data between 15 and 25
union all
select ’26-35′ [Data Range], count(*) [Count] from #temp where data between 26 and 35
union all
select ’36-45′ [Data Range], count(*) [Count] from #temp where data between 36 and 45
union all
select ’46-55′ [Data Range], count(*) [Count] from #temp where data between 46 and 55 Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

quote:Originally posted by ramkumar.mu
Is this what you are looking for??? select ’15-25′ [Data Range], count(*) [Count] from #temp where data between 15 and 25
union all
select ’26-35′ [Data Range], count(*) [Count] from #temp where data between 26 and 35
union all
select ’36-45′ [Data Range], count(*) [Count] from #temp where data between 36 and 45
union all
select ’46-55′ [Data Range], count(*) [Count] from #temp where data between 46 and 55 Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

Read the replies of mine and Frank’s Madhivanan Failing to plan is Planning to fail

sorry madhi, i couldnt find the difference. both looks the same for me except that my results are vertical where as Frank’s results looks horizontal Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

Your query looks very similar (except for the difference in the WHERE clause) to that Tahsin posted in the second reply. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

hmmm… i missed to see that…[B)] Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

It is sometimes frustrating when people try to help and the OP (original poster) does not respond to navigate the thread to a useful conclusion. Often, this may lead others to answer everything but the original OP’s concerns. That said: Based on the question and sample output, this is not a cross-tab report. And to everybody else wondering, the sample output is definitely not a SUM but a COUNT aggregation. It actually looks and feels like the OP is trying to generate a histogram. There are many ways of doing that; some have already been provided above(horizontal,vertical). Many more generic algorithmns exist to cater for this kind of thing. Could the moderator please lock this thread? Nathan H. Omukwenyi
MVP [SQL Server]

Let’s give the OP the chance to answer. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

>>the sample output is definitely not a SUM but a COUNT aggregation. All the examples given were based on Count Aggregation and not SUM [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan >>the sample output is definitely not a SUM but a COUNT aggregation. All the examples given were based on Count Aggregation and not SUM [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <hr height="1" noshade id="quote"></blockquote id="quote"> Sorry. Just trying to KILL the confusion/queries/doubts on the type of aggregation the OP’s sample output may have generated as evidenced by some of the questions above. Paraphrase: OPs not responding to their own posts is generally a good recipe for lots of ping pong. Not that I mind the game. Nathan H. Omukwenyi MVP [SQL Server]

The aggregation can be implemented in different ways depending on a situation. Afterall, 2+2+2+2 can be written in different ways. what is union anyway? May the Almighty God bless us all!
www.empoweredinformation.com

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> Afterall, 2+2+2+2 can be written in different ways. what is union anyway? <hr height="1" noshade id="quote"></blockquote id="quote"> Being picky, there is a slight, but important difference between UNION and UNION ALL which makes your example not the best one around. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] But I agree, the query can be written both as SUM or COUNT aggregation. Just look at my first reply here. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Thanks for all your help…actually i need count not sum. Any how i found the result little while after posting the question. Thank you . emamuthu

So what is the query you used? Madhivanan Failing to plan is Planning to fail

SELECT
(SELECT COUNT(a1) from agetest WHERE ages BETWEEN 15 AND 25) AS ’15-25′,
(SELECT COUNT( a1) from agetest WHERE ages BETWEEN 26 AND 35) AS ’26-35′,
(SELECT COUNT( a1) from agetest WHERE ages BETWEEN 36 AND 45) AS ’36-45′

SELECT
(SELECT COUNT(a1) from agetest WHERE ages BETWEEN 15 AND 25) AS ‘count’,’15-25′ as agerage
union all
select
(SELECT COUNT( a1) from agetest WHERE ages BETWEEN 26 AND 35) AS ‘count’,’26-35′ as agerage
union all
select
(SELECT COUNT( a1) from agetest WHERE ages BETWEEN 36 AND 45) AS ‘count’,’36-45′ as agerage display result as count agerage
—– ——–
315-25
226-35
236-45

You can create a table for the age ranges, or use a derived table like this: select x.agerange, count(*)
from age
inner join
(select 15 as minage, 25 as maxage, ’15-25′ as agerange
union all select 26, 35, ’26-35′
union all select 36, 45, ’36-45′) x
on age.ages between x.minage and x.maxage
group by x.agerange

thanks adriaan

]]>