Loading ...
Sorry, an error occurred while loading the content.

Count or aggregate problem

Expand Messages
  • Dermot
    Hi, I have a table (menu_items) that is meant to represent a nested data structure for a menu (HTML select, option and optgroup). I ve tried to adapt a
    Message 1 of 5 , Aug 1, 2012
    • 0 Attachment
      Hi,

      I have a table (menu_items) that is meant to represent a nested data
      structure for a menu (HTML select, option and optgroup). I've tried to
      adapt a structure where root menu item have no parent_id and sub-menu
      items have a parent_id that indicates it's parent (adapted from
      examples by Joe Celko).


      id | menu_id | parent_id | label
      ----+---------+-----------+--------------------------
      3 | 1 | 2 | sub menu item 1
      4 | 1 | 2 | sub menu item 4
      1 | 1 | | root item
      2 | 1 | | root item with sub items


      I am struggling to find a query that will render the data in a useful
      way. The chief problem is working out if a root item has children. A
      count of the children would be nice but isn't absolutely necessary. I
      can do:

      SELECT id , label, count(parent_id=0) AS children FROM menu_items
      GROUP BY id, label ;

      id | label | children
      ----+--------------------------+----------
      2 | root item with sub items | 0
      4 | sub menu item 4 | 1
      3 | sub menu item 1 | 1
      1 | root item | 0

      But I need it is the count of items to appear on the root item like this.

      2 | root item with sub items | 2


      Does anyone know how I can formula such as query?
      The DDL for the table is below. I could add a boolean column to say
      has_childern but the may be difficult to maintain so if it's possible
      to not do that I'd rather.

      Thanks in advance.
      Dermot.





      Column | Type | Modifiers
      -----------+------------------------+--------------------
      id | integer | not null
      menu_id | integer | not null default 0
      parent_id | integer |
      label | character varying(254) | not null
      Indexes:
      "menu_items_pkey" PRIMARY KEY, btree (id)
      Check constraints:
      "menu_items_check" CHECK (parent_id <> id)
      Foreign-key constraints:
      "menu_
    • Mark E
      A subquery isn t optimal but this may work for you: SELECT  mi1.id, mi1.menu_id, mi1.parent_id, mi1.label,                 (select COUNT(*) from
      Message 2 of 5 , Aug 1, 2012
      • 0 Attachment
        A subquery isn't optimal but this may work for you:

        SELECT  mi1.id, mi1.menu_id, mi1.parent_id, mi1.label,
                        (select COUNT(*) from menu_items mi2 where mi2.parent_id = mi1.id) as menu_ct
                    FROM    menu_items mi1
                    WHERE   mi1.parent_id IS NULL

        Mark



        ________________________________
        From: Dermot <paikkos@...>
        To: SQLQueriesNoCode@yahoogroups.com
        Sent: Wednesday, August 1, 2012 6:14 PM
        Subject: [SQLQueriesNoCode] Count or aggregate problem


         
        Hi,

        I have a table (menu_items) that is meant to represent a nested data
        structure for a menu (HTML select, option and optgroup). I've tried to
        adapt a structure where root menu item have no parent_id and sub-menu
        items have a parent_id that indicates it's parent (adapted from
        examples by Joe Celko).

        id | menu_id | parent_id | label
        ----+---------+-----------+--------------------------
        3 | 1 | 2 | sub menu item 1
        4 | 1 | 2 | sub menu item 4
        1 | 1 | | root item
        2 | 1 | | root item with sub items

        I am struggling to find a query that will render the data in a useful
        way. The chief problem is working out if a root item has children. A
        count of the children would be nice but isn't absolutely necessary. I
        can do:

        SELECT id , label, count(parent_id=0) AS children FROM menu_items
        GROUP BY id, label ;

        id | label | children
        ----+--------------------------+----------
        2 | root item with sub items | 0
        4 | sub menu item 4 | 1
        3 | sub menu item 1 | 1
        1 | root item | 0

        But I need it is the count of items to appear on the root item like this.

        2 | root item with sub items | 2

        Does anyone know how I can formula such as query?
        The DDL for the table is below. I could add a boolean column to say
        has_childern but the may be difficult to maintain so if it's possible
        to not do that I'd rather.

        Thanks in advance.
        Dermot.

        Column | Type | Modifiers
        -----------+------------------------+--------------------
        id | integer | not null
        menu_id | integer | not null default 0
        parent_id | integer |
        label | character varying(254) | not null
        Indexes:
        "menu_items_pkey" PRIMARY KEY, btree (id)
        Check constraints:
        "menu_items_check" CHECK (parent_id <> id)
        Foreign-key constraints:
        "menu_



        [Non-text portions of this message have been removed]
      • amit kumar
        1. Hi, Is there any way for below issue. I want to insert a row from one table to another, without mentioning columns name having similar schema, but for some
        Message 3 of 5 , Aug 18, 2012
        • 0 Attachment
          1. Hi,
          Is there any way for below issue.
          I want to insert a row from one
          table to another, without mentioning columns name having similar schema, but for
          some fields i want to use some local variables.
          I want to avoid to mention
          columns name because table have more than 900 columns.


          Thanks
          Amit Kumar Gupta....


          [Non-text portions of this message have been removed]
        • Mark E
          How would the query know what fields to pull data from or what fields in your new table to insert in to?  Unless you can copy and paste the record, I don t
          Message 4 of 5 , Aug 18, 2012
          • 0 Attachment
            How would the query know what fields to pull data from or what fields in your new table to insert in to? 


            Unless you can copy and paste the record, I don't think you'll have any choice. 


            Also, you may want to look in to why your table has so many columns.  900 seems extremely excessive and could probably be normalized to help with that.


            Mark


            ________________________________
            From: amit kumar <amit_so_cool@...>
            To: "SQLQueriesNoCode@yahoogroups.com" <SQLQueriesNoCode@yahoogroups.com>
            Sent: Saturday, August 18, 2012 9:07 AM
            Subject: Re: [SQLQueriesNoCode] Count or aggregate problem


             
            1. Hi,
            Is there any way for below issue.
            I want to insert a row from one
            table to another, without mentioning columns name having similar schema, but for
            some fields i want to use some local variables.
            I want to avoid to mention
            columns name because table have more than 900 columns.

            Thanks
            Amit Kumar Gupta....

            [Non-text portions of this message have been removed]




            [Non-text portions of this message have been removed]
          • John Warner
            If you are too lazy to type and a 900 column table, I can understand the problem (I would not have allowed a table to get that wide for exactly that reason or
            Message 5 of 5 , Aug 18, 2012
            • 0 Attachment
              If you are too lazy to type and a 900 column table, I can understand the
              problem (I would not have allowed a table to get that wide for exactly that
              reason or might resort to views). What RDBMS are you using, you might be
              able to use the database schema tables to 'do the typing' for you.

              John Warner


              -----Original Message-----
              From: SQLQueriesNoCode@yahoogroups.com
              [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of amit kumar
              Sent: Saturday, August 18, 2012 9:07 AM
              To: SQLQueriesNoCode@yahoogroups.com
              Subject: Re: [SQLQueriesNoCode] Count or aggregate problem

              1. Hi,
              Is there any way for below issue.
              I want to insert a row from one
              table to another, without mentioning columns name having similar schema, but
              for some fields i want to use some local variables.
              I want to avoid to mention
              columns name because table have more than 900 columns.


              Thanks
              Amit Kumar Gupta....


              [Non-text portions of this message have been removed]



              ------------------------------------

              Yahoo! Groups Links
            Your message has been successfully submitted and would be delivered to recipients shortly.