Professional Web Applications Themes

Count two distinct items in a table. - Microsoft SQL / MS SQL Server

I have a table with roughly the following values: (each restarant will have such a table) MenuItem Vegetarian ======== ========== Fish & Chips NO Beans YES Cake YES Beef NO Corn YES Steak NO Salad YES I need to compose a quesry that will give me a distinct count for all the Vegetarian stuff, and all the non-vegi stuff. (based on above example) Vegi = 4 Non-Vegi=3 I can figure that part out... My problem is, i need to determine if the vegetarian items outnumber the non-vegetarian items. Any ideas how I can determine this is a query? Thanks David...

  1. #1

    Default Count two distinct items in a table.

    I have a table with roughly the following values:
    (each restarant will have such a table)

    MenuItem Vegetarian
    ======== ==========
    Fish & Chips NO
    Beans YES
    Cake YES
    Beef NO
    Corn YES
    Steak NO
    Salad YES

    I need to compose a quesry that will give me a distinct count for all
    the Vegetarian stuff, and all the non-vegi stuff. (based on above
    example)

    Vegi = 4
    Non-Vegi=3

    I can figure that part out... My problem is, i need to determine if
    the vegetarian items outnumber the non-vegetarian items. Any ideas how
    I can determine this is a query?

    Thanks
    David
    David Guest

  2. #2

    Default Re: Count two distinct items in a table.

    If you want it as a 'vertical' resultset :

    SELECT Vegetarian, COUNT(Vegetarian)
    FROM tbl
    GROUP BY Vegetarian ;

    You can get it in a single row like:

    SELECT SUM(CASE Vegetarian WHEN 'YES' THEN 1 ELSE 0 END) AS "Vegi",
    SUM(CASE Vegetarian WHEN 'NO' THEN 1 ELSE 0 END) AS "Non-Vegi"
    FROM tbl ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: Count two distinct items in a table.

    Try,

    select case when NoVegCount > VegCount Then 'Non-Veg'
    when NoVegCount < VegCount Then 'Veg'
    else 'Both Veg'
    end
    from
    (select sum(case when Vegetarian = 'No' then 1 end) NoVegCount,
    sum(case when Vegetarian = 'Yes' then 1 end) VegCount
    from Test7) VegCount

    HTH
    Prasad

    "David" <com> wrote in message
    news:google.com... 


    Prasad Guest

  4. #4

    Default Re: Count two distinct items in a table.

    Try:

    select Vegetarian, Count(*) from theTable
    group by Vegetarian

    The reason why this works is because count() operates on the group, not the
    entire result set. You could get fancier and use a CASE statement to
    substitue "Vegetarian" for 'YES' and 'Non-Veggie' for 'NO'

    regards, -marty nicholson

    "David" <com> wrote in message
    news:google.com... 


    Martin Guest

Similar Threads

  1. distinct, count
    By Frances in forum MySQL
    Replies: 3
    Last Post: December 2nd, 07:42 PM
  2. Needed, COUNT(DISTINCT *) workaround for ACCESS
    By rickaclark54 in forum Macromedia ColdFusion
    Replies: 4
    Last Post: March 14th, 08:07 PM
  3. PageCount too high for Items.Count
    By Keith Patrick in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: June 9th, 03:48 PM
  4. Replies: 3
    Last Post: April 18th, 12:52 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 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