Professional Web Applications Themes

Summarizing a column to a DISTINCT value - MySQL

Hey folks, I am just getting into MySQL and have run into a problem with subqueries. I am running MySQL 4.0.21 and I am trying to CONCAT and I have no idea where I'm tripping up... The data looks like this.... +----+------------+---------+ | id | product | color | +----+------------+---------+ | 1 | mixer | blue | | 2 | mixer | green | | 3 | mixer | yellow | +----+------------+ I need to generate this.... +----+------------+---------+ | id | product | color | +----+------------+---------+ | 1 | mixer | blue/green/yellow | +----+------------+ In short, for each distinct product, ...

  1. #1

    Default Summarizing a column to a DISTINCT value

    Hey folks,
    I am just getting into MySQL and have run into a problem with
    subqueries.
    I am running MySQL 4.0.21 and I am trying to CONCAT and I have no idea
    where I'm tripping up...

    The data looks like this....

    +----+------------+---------+
    | id | product | color |
    +----+------------+---------+
    | 1 | mixer | blue |
    | 2 | mixer | green |
    | 3 | mixer | yellow |
    +----+------------+

    I need to generate this....
    +----+------------+---------+
    | id | product | color |
    +----+------------+---------+
    | 1 | mixer | blue/green/yellow |
    +----+------------+

    In short, for each distinct product, generate a color field where the
    all the choices are concatenated by a slash.

    My apologies if this is remarkably simple. The real world query is
    actually more brutal than this one which I have reduced for simplicity.

    Sherabg

    worldcyclist@gmail.com Guest

  2. #2

    Default Re: Summarizing a column to a DISTINCT value

    com wrote: 

    You need MySQL 4.1 to do this. The solution is:

    SELECT MIN(id), product,
    GROUP_CONCAT(color ORDER BY color SEPARATOR '/')
    FROM tablename
    GROUP BY product;

    See doentation on GROUP_CONCAT() here:
    http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html

    There is no alternative for GROUP_CONCAT() in MySQL 4.0. If you can't
    upgrade, you'll have to fetch all the data and do the grouping in
    application code.

    Regards,
    Bill K.
    Bill Guest

Similar Threads

  1. Replies: 0
    Last Post: February 9th, 02:06 AM
  2. Replies: 0
    Last Post: November 11th, 02:31 PM
  3. Replies: 0
    Last Post: September 26th, 10:27 PM
  4. Perl Summarizing Script for Traffic
    By Peter Kolbe in forum PERL Beginners
    Replies: 0
    Last Post: November 6th, 11:02 AM
  5. Insert into <table w/ text column> select distinct ...
    By Adam Nester in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 04:38 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