Professional Web Applications Themes

how do I use COUNT? - ASP Database

I'm doing this: SELECT COUNT(injury.classCode = 'lti') FROM dbo.tblInjury injury WHERE injury.classCode = 'fatality'...

  1. #1

    Default how do I use COUNT?

    I'm doing this:
    SELECT
    COUNT(injury.classCode = 'lti')
    FROM
    dbo.tblInjury injury
    WHERE
    injury.classCode = 'fatality'
    ben Guest

  2. #2

    Default Re: how do I use COUNT?

    sorry, sent it too early.

    I'm doing this:
    SELECT
    fat = COUNT(*)
    FROM
    dbo.tblInjury injury
    WHERE
    injury.classCode = 'fatality'

    SELECT
    lti = COUNT(*)
    FROM
    dbo.tblInjury injury
    WHERE
    injury.classCode = 'lti'



    Can I do it in one statement?
    ben Guest

  3. #3

    Default Re: how do I use COUNT?

    On Tue, 14 Sep 2004 14:30:07 +1000, ben h <com.invalid>
    wrote:
     

    yes I think you can.


    SELECT
    fat = SUM(CASE WHEN injury.classCode = 'fatality' THEN 1 ELSE 0
    END)
    lti = SUM(CASE WHEN injury.classCode = 'lti' THEN 1 ELSE 0 END)
    FROM
    dbo.tblInjury injury
    WHERE
    injury.classCode = 'lti'
    OR injury.classCode = 'fatality'

    (Untested as you didn't provide any DDL or sample data)

    http://www.aspfaq.com/etiquette.asp?id=5006


    Not sure if its better than doing 2 sperate counts, but you'll have to
    test that on your data.

    HTH

    Al.

    Harag Guest

  4. #4

    Default Re: how do I use COUNT?

    Are you trying to get these results back to the ASP page, or do you only
    need them in the local variables in the stored procedure?

    If you are bringing them back to the ASP page, another approach could be:

    SELECT ClassCode, COUNT(*)
    FROM dbo.tblInjury
    WHERE classCode IN ('fatality', 'lti')
    GROUP BY ClassCode

    You'd have a row for each ClassCode you were interested in...

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "ben h" <com.invalid> wrote in message
    news:#phx.gbl... 


    Aaron Guest

  5. #5

    Default Re: how do I use COUNT?

    Aaron [SQL Server MVP] wrote:
     

    Thanks Aaron, I'm needing them as local vars within the SP.
    ben Guest

  6. #6

    Default Re: how do I use COUNT?

    Harag wrote:

     
    >
    >
    > yes I think you can.
    >
    >
    > SELECT
    > fat = SUM(CASE WHEN injury.classCode = 'fatality' THEN 1 ELSE 0
    > END)
    > lti = SUM(CASE WHEN injury.classCode = 'lti' THEN 1 ELSE 0 END)
    > FROM
    > dbo.tblInjury injury
    > WHERE
    > injury.classCode = 'lti'
    > OR injury.classCode = 'fatality'
    >
    > (Untested as you didn't provide any DDL or sample data)
    >
    > http://www.aspfaq.com/etiquette.asp?id=5006
    >
    >
    > Not sure if its better than doing 2 sperate counts, but you'll have to
    > test that on your data.
    >
    > HTH
    >
    > Al.
    >[/ref]

    Thanks Al,
    that looks good.

    btw, I only provide enough info for you to solve my problem.

    (kidding)
    ben Guest

Similar Threads

  1. Need Help with Count
    By TylerStonestreet in forum Macromedia ColdFusion
    Replies: 3
    Last Post: February 26th, 06:58 PM
  2. Count ()
    By Angelosalsa in forum Coldfusion Database Access
    Replies: 0
    Last Post: February 17th, 03:57 AM
  3. COUNT(*)
    By Craig Roberts in forum PHP Development
    Replies: 0
    Last Post: August 1st, 08:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139