Post reply

Help SQL query

  • January 22, 2013 at 4:27 am

    #267531

    hI guys i need to know how many learners there are when 3 columns A, b, and c are added together you get greater value than 100.

    eg

    learner A B C TOTAL

    MAM 50 100 NULL 150

    ANG 20 20 65 105

    etc PLEASE HELP

  • January 22, 2013 at 4:34 am

    #1579543

    its bit confusing what you need .

    PLease post the table definition along with sample data and expected output. then only somebody can help you .

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • January 22, 2013 at 4:38 am

    #1579546

    SELECT COUNT(*) FROM

    WHERE A+B+C > 100

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 22, 2013 at 4:39 am

    #1579547

    Basically the table looks like this

    learner a b c

    mam 100 50 null

    ang 20 20 65

    brad 25 25 50

    i want an output that looks like this

    learner a b c total

    mam 100 50 null 150

    ang 20 20 65 105

    i only want the query to display any totals that are greater that 100

  • January 22, 2013 at 4:40 am

    #1579548

    what does the &gt mean

  • January 22, 2013 at 4:42 am

    #1579552

    SELECT learner,a, b, c, ISNULL(a,0)+ISNULL(b,0)+ISNULL(c,0) AS [total] FROM

    WHERE ISNULL(a,0)+ISNULL(b,0)+ISNULL(c,0) > 100

    Edited to fix null issue

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 22, 2013 at 4:42 am

    #1579551

    with cteSSC (leaner, A, B, C)

    as (select 'MAM', 50, NULL, 100 union all

    select 'ANG', 20, 65, 105 union all

    select 'DSD', 20, NULL, 20)

    select *

    from cteSSC

    where ISNULL(A, 0)+ISNULL(B, 0)+ISNULL(C, 0) > 100

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • January 22, 2013 at 4:43 am

    #1579554

    this is not taking null values into account my friend

  • January 22, 2013 at 4:46 am

    #1579555

    Use the ISNULL function (see above)

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • January 22, 2013 at 4:47 am

    #1579557

    thanks for your help david but this doesnt take the null values into account

  • January 22, 2013 at 4:49 am

    #1579559

    Yep, my bad :blush:

    Changed my second solution to cater for nulls

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 22, 2013 at 4:51 am

    #1579560

    SUCCESS Thanks for all your help Guys And Girls its worked Thanks again

  • January 22, 2013 at 5:50 am

    #1579580

    mamzy.rahman (1/22/2013)

    thanks for your help david but this doesnt take the null values into account

    http://msdn.microsoft.com/en-us/library/ms184325.aspx

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply