SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select with dynamic group by

    I have two kinds in my table, movie and tv show. I want to be able to GROUP BY depending on what type it is. It's easy enough if I want to display one or the other:

    Code MySQL:
    select title from table where kind='movie' group by movie_title
    select title from table where kind='tv' group by show_name

    what can i do if i want to display all records in one query?

    something like:
    Code MySQL:
    select title from table where kind='tv' group by show_name, kind='movie' group by movie_title

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please show a couple of rows of data from each table

    i'm having a hard time understanding why you want to use GROUP BY

    are you sure maybe you shouldn't be using ORDER BY instead?

    GROUP BY is for collapsing multiple rows into one

    it sounds instead like you still want to show individual rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    please show a couple of rows of data from each table

    i'm having a hard time understanding why you want to use GROUP BY

    are you sure maybe you shouldn't be using ORDER BY instead?

    GROUP BY is for collapsing multiple rows into one

    it sounds instead like you still want to show individual rows
    There are multiple rows with the same title. Movie remakes, an entire season of tv shows etc.

    These should be grouped by show_name (kind='tv')
    LOST Season 1 (show_name)
    episode 1 (title)
    episode 2 (title)
    episide 3 (title)

    These should be grouped by title (kind='movie')
    Flight of the Phoenix (original Version)
    Flight of the Phoenix (Remake)
    Frankenstein (original version)
    Frankenstein (remake)

    So the in the result the user sees
    LOST Season 1
    Flight of the Phoenix
    Frankenstein

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, thanks, greate sample data
    Code:
    SELECT movie_title 
      FROM table 
     WHERE kind='movie' 
    GROUP BY movie_title
    UNION ALL
    SELECT show_name 
      FROM table 
     WHERE kind='tv' 
    GROUP BY show_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, thanks, greate sample data
    Code:
    SELECT movie_title 
      FROM table 
     WHERE kind='movie' 
    GROUP BY movie_title
    UNION ALL
    SELECT show_name 
      FROM table 
     WHERE kind='tv' 
    GROUP BY show_name
    Thank you!

    Sorry! I was completely beat last night and flaked out about the sample data.

    What you gave me works, except that it lists the movies first, then the tv shows. is that what it's supposed to do? Sounds logical, given the query.

    Is there a way to mix them so that they all appear together in alphabetical order?
    Code MySQL:
    CREATE TABLE `movies` (
      `id` bigint(20) unsigned NOT NULL auto_increment,
      `amazon_asin` varchar(32) collate utf8_unicode_ci NOT NULL default '',
      `kind` varchar(11) collate utf8_unicode_ci NOT NULL default '',
      `title` varchar(255) collate utf8_unicode_ci NOT NULL default '',
      `title_slug` varchar(255) collate utf8_unicode_ci NOT NULL default '',
      `rel_date` varchar(20) collate utf8_unicode_ci NOT NULL default '',
      `category` varchar(32) collate utf8_unicode_ci NOT NULL default '',
      `rating` varchar(10) collate utf8_unicode_ci NOT NULL default '',
      `director` varchar(255) collate utf8_unicode_ci NOT NULL default '',
      `producer` varchar(255) collate utf8_unicode_ci NOT NULL default '',
      `cast` text collate utf8_unicode_ci NOT NULL,
      `screenwriter` varchar(255) collate utf8_unicode_ci NOT NULL default '',
      `short_description` text collate utf8_unicode_ci NOT NULL,
      `long_description` text collate utf8_unicode_ci NOT NULL,
      `show_name` varchar(60) collate utf8_unicode_ci NOT NULL default '',
      `show_name_slug` varchar(32) collate utf8_unicode_ci NOT NULL,
      `season` varchar(4) collate utf8_unicode_ci NOT NULL default '',
      `episode` tinyint(3) unsigned NOT NULL,
      `episode_id` varchar(32) collate utf8_unicode_ci NOT NULL default '',
      `tv_network` varchar(32) collate utf8_unicode_ci NOT NULL default '',
      `sort_name` varchar(255) collate utf8_unicode_ci NOT NULL default '',
      `sort_artist` text collate utf8_unicode_ci NOT NULL,
      `sort_album_artist` varchar(255) collate utf8_unicode_ci NOT NULL default '',
      `sort_album` varchar(255) collate utf8_unicode_ci NOT NULL default '',
      `sort_show` varchar(255) collate utf8_unicode_ci NOT NULL default '',
      `advisory` varchar(32) collate utf8_unicode_ci NOT NULL,
      `copyright` varchar(255) collate utf8_unicode_ci NOT NULL default '',
      `comments` text collate utf8_unicode_ci NOT NULL,
      `track_num` varchar(4) collate utf8_unicode_ci NOT NULL default '',
      `track_total` varchar(4) collate utf8_unicode_ci NOT NULL default '',
      `img_lg` varchar(255) collate utf8_unicode_ci NOT NULL default '',
      `img_sm` varchar(255) collate utf8_unicode_ci NOT NULL default '',
      `added_uid` bigint(20) unsigned NOT NULL default '0',
      `edited_uid` bigint(20) unsigned NOT NULL default '0',
      `dev_id` int(10) unsigned NOT NULL,
      `date_added` datetime NOT NULL default '0000-00-00 00:00:00',
      `date_edited` datetime NOT NULL default '0000-00-00 00:00:00',
      `total_chapters` tinyint(3) unsigned NOT NULL,
      `locked` char(1) collate utf8_unicode_ci NOT NULL default '0',
      PRIMARY KEY  (`id`),
      FULLTEXT KEY `show_name` (`show_name`,`title`),
      FULLTEXT KEY `title` (`title`,`rel_date`,`director`,`producer`,`screenwriter`,`cast`,`show_name`,`tv_network`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3837 ;
     
    (208, '', 'TV Show', 'Exodus, Part 2', 'exodus-part-2', '2005-05-25T04:00:00Z', 'Drama', 'TV-14', 'Jack Bender', '', 'cast', 'short_desc', 'long_desc', 'LOST', 'lost', '1', 24, '122A', 'ABC', 'Exodus, Part 2', 'LOST', '', 'LOST, Season 1', 'LOST', '', '', '', '24', '25', 'img', 'img', 0, 0, 3, '2008-04-14 14:59:24', '0000-00-00 00:00:00', 11, '0'),
    (209, '', 'TV Show', 'Exodus, Part 1', 'exodus-part-1', '2005-05-18T04:00:00Z', 'Drama', 'TV-14', 'Jack Bender', '', 'cast', '', 'The French woman ', 'LOST', 'lost', '1', 23, '121', 'ABC', 'Exodus, Part 1', 'LOST', '', 'LOST, Season 1', 'LOST', '', '', '', '23', '25', 'img', 'img', 0, 0, 3, '2008-04-14 14:59:26', '0000-00-00 00:00:00', 8, '0'),
     
    (841, '', 'Movie', 'Ghostbusters', 'ghostbusters', '1984-06-08T00:00:00Z', 'Comedy', 'PG', 'Ivan Reitman', '', 'cast', 'short_desc', 'long_desc', '', '', '', 0, '', '', 'Ghostbusters', 'cast', '', '', '', '', '', '', '', '', 'img', 'img', 3, 3, 3, '2008-04-14 16:25:08', '2008-04-15 14:49:06', 28, '1'),
    (1045, '', 'Movie', 'Ghostbusters', 'ghostbusters-0', '1984-06-08T00:00:00Z', 'Comedy', 'PG', 'Ivan Reitman', '', 'cast', 'Dan Aykroyd, Harold Ramis', 'short', 'long', '', '', '', 0, '', '', 'Ghostbusters', 'cast', '', '', '', '', '', '', '', '', 'img', 'img', 0, 0, 3, '2008-04-14 19:47:18', '0000-00-00 00:00:00', 0, '0'),

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tgavin View Post
    Is there a way to mix them so that they all appear together in alphabetical order?
    yes, add this to the end of the query:
    Code:
    ORDER BY 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, add this to the end of the query:
    Code:
    ORDER BY 1
    that ordered them by id (just using the * for testing)

    Code MySQL:
    SELECT * FROM movies 
    WHERE kind='Movie' 
    GROUP BY title 
    UNION ALL 
    SELECT * FROM movies 
    WHERE kind='TV Show' 
    GROUP BY show_name 
    ORDER BY 1

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you can't do that! you can't ask a question, get an answer, and then change the question! and you can't change the SQL and expect it to continue to work!



    whew! sorry for the rant



    please note, you cannot use the dreaded, evil "select star" at the same time as GROUP BY

    it just doesn't make any sense

    having now seen your table layout, i am at a loss to know why you are even grouping in the first place

    i'm sorry, i don't understand what you're doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you can't do that! you can't ask a question, get an answer, and then change the question! and you can't change the SQL and expect it to continue to work!



    whew! sorry for the rant



    please note, you cannot use the dreaded, evil "select star" at the same time as GROUP BY

    it just doesn't make any sense

    having now seen your table layout, i am at a loss to know why you are even grouping in the first place

    i'm sorry, i don't understand what you're doing
    I didn't realize I changed the question. All I did was provide very basic syntax in my first post, because I figured it was a simple query. No point in bogging the post down with tons of unnecessary sql code. Guess I was wrong.

    I'm trying to do what I asked in my first question. I have tv shows and movies. The title of the movie is the 'title' field. The episode title of the tv show is also the 'title' field. The name of the tv show is the 'show_name' field.

    When showing results on the page, I want to group all of the tv shows with the same show name together, and all of the movies with the same movie name together.

    The query you provided worked. It just shows all of the movies first, then the tv shows.
    title a (movie)
    title b (movie)
    title c (movie)
    show_name a (tv show)
    show_name b (tv show)
    show_name c (tv show)

    I'd like it to be

    title a
    show_name a
    title b
    show_name b
    title c
    show_name c

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT title_or_name
         , movie_or_show
      FROM (
           SELECT movie_title AS title_or_name
                , 'movie'     AS movie_or_show
             FROM table 
            WHERE kind='movie' 
           GROUP BY movie_title
           UNION ALL
           SELECT show_name 
                , 'tv'
             FROM table 
            WHERE kind='tv' 
           GROUP BY show_name
           ) AS t
    ORDER
        BY title_or_name
         , movie_or_show
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    perfect! thank you very much!


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
  •