www.webdeveloper.com
Results 1 to 3 of 3

Thread: I need a MySQL query (probably simple for experienced users)

Hybrid View

  1. #1
    Join Date
    May 2009
    Location
    Rochester, NY
    Posts
    364

    I need a MySQL query (probably simple for experienced users)

    I'm sure this is simple for database experts, but I am just a novice user.

    I want to count the FREQUENCY of the color occurrences in my database. Basically, col ID is unique, num is my object, color is the color of each object, and str is a string I've collected for each one. What I want to do is write a MySQL query which can run in PHP to count the occurrences of each color, tallying only once for each object.

    So if I have this:

    Code:
    
    id    num    color    str
    0    1      1        a
    1    1      1        sdf
    2    1      1        sdfsd
    3    1      1        asd
    4    2      2        fd
    5    2      2        dsss
    6    3      1        dsdew
    7    4      2        ds
    8    4      2        ccd
    9    5      1        css
    
    I want to have the output:
    Color 1: 3
    Color 2: 2

    Since there are 3 objects which have color 1 and 2 which have color 2.

    Please, could someone help my write my query?


  2. #2
    Join Date
    Aug 2009
    Posts
    30
    Code:
    SELECT num, COUNT(num) AS count
    FROM table_name
    GROUP BY num
    should give you as many lines as different objects you have containing the objects name and the number of its appearance

    could not test it, but i am optimistic that it is quite fine

    cu
    huckepick
    Real power can't be given. It must be taken.

  3. #3
    Join Date
    May 2009
    Location
    Rochester, NY
    Posts
    364
    Ok, I used your query and it told me how many nums I have. I modified it to read:

    Code:
    SELECT color, COUNT(color) AS count
    FROM table_name
    GROUP BY color
    and that told me how many colors I have.

    However, I want to count the combination of color to number. I think I will write some php to count from a basic select statement.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles