SQL Server Performance

T-sql query

Discussion in 'General Developer Questions' started by emamuthu, May 8, 2006.

  1. emamuthu New Member

    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

  2. druer New Member

    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.
  3. Tahsin New Member

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

    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
  5. Madhivanan Moderator

    Look for cross-tab reports in sql server help file

    Madhivanan

    Failing to plan is Planning to fail
  6. FrankKalis Moderator

    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)
  7. ramkumar.mu New Member


    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..."
  8. Madhivanan Moderator

    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
  9. ramkumar.mu New Member

    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..."
  10. FrankKalis Moderator

    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=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  11. ramkumar.mu New Member

    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..."
  12. vbkenya New Member

    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]
  13. FrankKalis Moderator

    Let's give the OP the chance to answer. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  14. Madhivanan Moderator

    &gt;&gt;the sample output is definitely not a SUM but a COUNT aggregation.<br /><br />All the examples given were based on Count Aggregation and not SUM [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  15. vbkenya New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br />&gt;&gt;the sample output is definitely not a SUM but a COUNT aggregation.<br />All the examples given were based on Count Aggregation and not SUM [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />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.<br /><br />Paraphrase: OPs not responding to their own posts is generally a good recipe for lots of ping pong. Not that I mind the game.<br /><br />Nathan H. Omukwenyi<br />MVP [SQL Server]<br />
  16. cmdr_skywalker New Member

    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
  17. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Afterall, 2+2+2+2 can be written in different ways. what is union anyway?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />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=';)' />]<br /><br />But I agree, the query can be written both as SUM or COUNT aggregation. Just look at my first reply here.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  18. emamuthu New Member

    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
  19. Madhivanan Moderator

    So what is the query you used?

    Madhivanan

    Failing to plan is Planning to fail
  20. emamuthu New Member

    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'
  21. emamuthu New Member

    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

  22. Adriaan New Member

    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
  23. emamuthu New Member

    thanks adriaan

Share This Page