Professional Web Applications Themes

Counting in three different queries or one? - Microsoft SQL / MS SQL Server

Try: select sum (case when FirstName like 'A%' then 1 else 0 end) , sum (case when LastName like 'D%' then 1 else 0 end) , sum (case when CustomerID > 55 then 1 else 0 end) from MyTable -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql "Tomer" <com> wrote in message news:phx.gbl... Hi all, Please help me to figure out, if it is possible or efficient to make the following in one query: On the same table, return the number of items in groups, where each ...

  1. #1

    Default Re: Counting in three different queries or one?

    Try:

    select
    sum (case when FirstName like 'A%' then 1 else 0 end)
    , sum (case when LastName like 'D%' then 1 else 0 end)
    , sum (case when CustomerID > 55 then 1 else 0 end)
    from
    MyTable

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Tomer" <com> wrote in message news:phx.gbl...
    Hi all,



    Please help me to figure out, if it is possible or efficient to make the
    following in one query:



    On the same table, return the number of items in groups, where each group is
    a result of different filter.



    For example: In a Customer table retrieve the following counters (Using
    COUNT)

    Number of customers that their First name starts with A

    Number of customers that their Last name starts with D

    Number of customers that their CustomerID is greater then 55



    Should I separate this to three different queries?



    Thanks,

    Tomer




    Tom Guest

  2. #2

    Default Re: Counting in three different queries or one?

    Try this

    Select NoOfCustFNStartA = count ( Case when FirstName like 'A%' then 1 else
    0 end)
    , NoOfCustLNStartD = count ( Case when LastName like 'D%' then 1
    else 0 end)
    , NoOfCustIDGtr55 = count ( Case when CustomerId>55 then 1 else
    0 end)
    from table_name

    HTH,
    Srinivas Sampangi
    "Tomer" <com> wrote in message
    news:phx.gbl... 
    is 


    sampangi Guest

  3. #3

    Default Re: Counting in three different queries or one?

    sorry it should be Sum of instead of Count

    Select NoOfCustFNStartA =sum ( Case when FirstName like 'A%' then 1 else
    0 end)
    , NoOfCustLNStartD = sum ( Case when LastName like 'D%' then 1
    else 0 end)
    , NoOfCustIDGtr55 = sum ( Case when CustomerId>55 then 1 else
    0 end)
    from table_name

    HTH
    Srinivas Sampangi



    "sampangi" <com> wrote in message
    news:OcuN5#phx.gbl... 
    else 
    else [/ref]
    group 
    >
    >[/ref]


    sampangi Guest

  4. #4

    Default Counting in three different queries or one?

    Hi all,



    Please help me to figure out, if it is possible or efficient to make the
    following in one query:



    On the same table, return the number of items in groups, where each group is
    a result of different filter.



    For example: In a Customer table retrieve the following counters (Using
    COUNT)

    Number of customers that their First name starts with A

    Number of customers that their Last name starts with D

    Number of customers that their CustomerID is greater then 55



    Should I separate this to three different queries?



    Thanks,

    Tomer



    Tomer Guest

Similar Threads

  1. counting rows
    By WOLFcfm in forum Coldfusion Database Access
    Replies: 7
    Last Post: December 26th, 09:23 PM
  2. Queries Of Queries Single Quote Problem
    By TimH2O in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 1st, 07:46 PM
  3. counting down in a for
    By drowl@23.me.uk in forum PERL Beginners
    Replies: 4
    Last Post: December 12th, 04:14 PM

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