Professional Web Applications Themes

count() - MySQL

Hello, I have a table like tho following (I've simplified it): ID WORD __________ 1 car 1 moto 1 wheel 2 house 2 apartment .. .... 'ID' and 'word' are the "primary key". Is it possible with one istruction to know how many WORDS there are for each ID? I'd want to obtain something like: ID TOT_WORDS ______________ 1 3 2 2 .. ... -- rob4you...

  1. #1

    Default count()

    Hello,

    I have a table like tho following (I've simplified it):

    ID WORD
    __________
    1 car
    1 moto
    1 wheel
    2 house
    2 apartment
    .. ....


    'ID' and 'word' are the "primary key".

    Is it possible with one istruction to know how many WORDS there are for
    each ID?
    I'd want to obtain something like:

    ID TOT_WORDS
    ______________
    1 3
    2 2
    .. ...


    --
    rob4you


    rob4you Guest

  2. #2

    Default Re: count()

    On 22 Feb, 15:29, rob4you <IT> wrote: 

    SELECT `id`, count(*) `tot_words`
    FROM table
    GROUP BY `id`

    Captain Guest

  3. #3

    Default Re: count()

    > SELECT `id`, count(*) `tot_words` 

    Thank you very much!

    I forgot the GROUP BY, so I had an error.

    You're always precise in your post. :-)

    --
    rob4you


    rob4you Guest

  4. #4

    Default Re: count()

    On 22 Feb, 16:45, rob4you <IT> wrote: 
    I am, in what way? is that good?

    Captain Guest

  5. #5

    Default Re: count()

    Sorry, I've another doubt:

    if the table is like that:

    ID WORD TIMES
    _________________
    1 car 4
    1 moto 5
    1 wheel 2
    2 house 3
    2 apart 1
    2 car 2
    .. .... .

    If I'd want to obtain something like that:

    ID WORD TIMES TOTAL
    _________________________
    1 car 4 3
    2 car 2 3
    .. ... . .

    That is:
    - first field: ID
    - second field: word (for example I can specify it with the WHERE
    clause)
    - third field: number of times the WORD appears in its own ID
    - last field: number of TOTAL words which have the same ID

    Is it possible?

    --
    rob4you


    rob4you Guest

  6. #6

    Default Re: count()

    rob4you wrote: 

    SELECT `ID`, COUNT(`WORD`) AS TOT_WORDS
    GROUP BY ID
    ORDER BY ID;

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: count()

    > SELECT `ID`, COUNT(`WORD`) AS TOT_WORDS 

    Do you have an answer also to the other question I've posed?

    --
    rob4you


    rob4you Guest

  8. #8

    Default Re: count()

    rob4you wrote: 

    I don't think it would be possible as you have it laid out. The problem
    is you want a count for one id/word combination, but in the same result
    you want the count for all the words in the id.

    If you put your word in the WHERE clause, SQL will filter out all the
    other words in the id. I'd suggest two SQL statements.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  9. #9

    Default Re: count()

    rob4you wrote: 

    I don't understand what "number of TOTAL words which have the same ID"
    It makes no sense.

    Please offer sample data complete with sample results so that I can see what
    you mean.


    Paul Guest

  10. #10

    Default Re: count()

    > Please offer sample data complete with sample results so that I can see what 

    Ok, i'll try. :-)

    Tabele 'words' contains these data:

    catID word occurrencies
    _________________________________
    1 audi 154
    1 alfa 95
    1 bmw 132
    2 wheels 56
    2 audi 80
    3 ferrari 15

    Knowing just the word I want to search (e.g. 'audi'), I want to obtain,
    if possible with a single query (or with some sub-queries), the
    following results:


    catID word occurr word_tot tot_in_catID
    __________________________________________________ ______
    1 audi 154 (154+80) 3
    2 audi 80 (154+80) 2


    That is, I want to get:

    - the categories (catID) where the word 'audi' appears
    - the word 'audi' (of course 'audi' is the word I already know)
    - the number of occurencies of the word 'audi' according to each
    category (this is exactly the 'occurrencies' field in the main table)
    - the sum of occurrencies fields where there is the word 'audi' in the
    various categories (that is 154+80). Of course this value is the same
    for each row of the results.
    - the total number of words in each category (as you can see in
    category 1 the words are '3', in category 2 they are '2').

    Can you help me?

    --
    rob4you


    rob4you Guest

Similar Threads

  1. Count(*) different than record count
    By Bosconian in forum MySQL
    Replies: 6
    Last Post: October 5th, 11:21 PM
  2. Count ()
    By Angelosalsa in forum Coldfusion Database Access
    Replies: 0
    Last Post: February 17th, 03:57 AM
  3. [PHP] COUNT(*)
    By Richard Baskett in forum PHP Development
    Replies: 0
    Last Post: August 1st, 08:39 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