Professional Web Applications Themes

SQL help with COUNT() - MySQL

I have a table that has an ID column, an expiration DATE column and a status column. I want to find out how many records will be expiring soon for each status. The first step, to find out how many records per status is easy. select count(ID), status from mytable GROUP BY Status; But I want to have a third column that tells me how many of those ID's per status will expire soon. So i have the following syntax TO_DAYS(Expiration) - TO_DAYS(NOW()) AS ExpDays All well and good so far. But I also want to know how many will ...

  1. #1

    Default SQL help with COUNT()


    I have a table that has an ID column, an expiration DATE column and a
    status column. I want to find out how many records will be expiring
    soon for each status.

    The first step, to find out how many records per status is easy.

    select count(ID), status from mytable GROUP BY Status;

    But I want to have a third column that tells me how many of those ID's
    per status will expire soon.

    So i have the following syntax

    TO_DAYS(Expiration) - TO_DAYS(NOW()) AS ExpDays

    All well and good so far. But I also want to know how many will be
    expiring in 7 days.

    ---------
    TO_DAYS(Expiration) - TO_DAYS(NOW())<=7, gives me 1's and 0's but i
    don't know how to COUNT() those.

    Just wrapping with a COUNT() funtion doesn't work or a count with =0
    also doesn't work.

    If I don't group then I can do something like this
    select IF(TO_DAYS(Expiration) - TO_DAYS(NOW())<=7,'Expiring
    Soon','Active') As ExpDays, status from mytable;

    But I'd rather the counts per status and i don't know how to do that.

    In short if i have 12 records for status =1 with 4 expiring soon then
    i'd like 3 columns and 1 row per status like this:
    '12' '4' '1' namely 12 total, 4 expiring soon and status = 1

    Ideas?

    misfit.joy@gmail.com Guest

  2. #2

    Default Re: SQL help with COUNT()

    com wrote: 

    A COUNT() of 1's is equivalent to SUM():

    SELECT SUM(TO_DAYS(Expiration) - TO_DAYS(NOW())<=7) AS count_expiring,
    status
    FROM mytable
    GROUP BY status

    Regards,
    Bill K.
    Bill Guest

  3. #3

    Default Re: SQL help with COUNT()

    Thanks so much. It's simple when you describe it but i was just not
    getting that. I didn't think of using SUM that way - thanks for
    sharing.

    d



    Bill Karwin wrote: 
    >
    > A COUNT() of 1's is equivalent to SUM():
    >
    > SELECT SUM(TO_DAYS(Expiration) - TO_DAYS(NOW())<=7) AS count_expiring,
    > status
    > FROM mytable
    > GROUP BY status
    >
    > Regards,
    > Bill K.[/ref]

    misfit.joy@gmail.com Guest

Similar Threads

  1. how to count?
    By Merlin in forum MySQL
    Replies: 1
    Last Post: May 26th, 09:23 PM
  2. Need Help with Count
    By TylerStonestreet in forum Macromedia ColdFusion
    Replies: 3
    Last Post: February 26th, 06:58 PM
  3. Count ()
    By Angelosalsa in forum Coldfusion Database Access
    Replies: 0
    Last Post: February 17th, 03:57 AM
  4. count
    By dennis in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 7th, 07:26 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