Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006
    Posts
    56

    Unanswered: Two Aggregation Necessary?

    Hello,

    I have this many_codes table that keeps a lot of codes that consists of up to four capital Latin alphabets. Eg, ABC, DED, XHZ, TRMD and so on.

    I want to find out how often each character is used in which position (from 1 to 4). Total counts are also needed.

    I have managed to write the SQL but I don't like the fact that I have to GROUP BY twice. Is there any better solution, please?
    Code:
    SELECT symbol
         , SUM(CASE WHEN digit = 1
                    THEN subtotal
                    ELSE 0 END) AS 1st
         , SUM(CASE WHEN digit = 2
                    THEN subtotal
                    ELSE 0 END) AS 2nd
         , SUM(CASE WHEN digit = 3
                    THEN subtotal
                    ELSE 0 END) AS 3rd
         , SUM(CASE WHEN digit = 4
                    THEN subtotal
                    ELSE 0 END) AS 4th
         , SUM(Subtotal) AS Total
      FROM (SELECT SUBSTR(code, digits.digit, 1) AS symbol
                 , digits.digit
                 , COUNT(*) subtotal
              FROM many_codes
             INNER JOIN digits
             WHERE digits.digit BETWEEN 1 AND 4
               AND SUBSTR(code, digits.digit, 1) <> ''
             GROUP BY SUBSTR(code, digits.digit, 1), digits.digit) base
     GROUP BY symbol;
    NB. Table digits has only one row, digit. This field stores integers.
    Field code belongs to Table many_codes.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Ikviens View Post
    ... but I don't like the fact that I have to GROUP BY twice.
    use WITH ROLLUP on your (single) GROUP BY

    Quote Originally Posted by Ikviens View Post
    NB. Table digits has only one row, digit. This field stores integers.
    only one column

    this is essentially a numbers table, congrats for coming up with this as part of your solution

    the numbers table will come in so handy in other places that you might consider actually making it permanent
    Code:
    CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY );
    INSERT INTO numbers ( n ) VALUES 
     (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),... ;
    note that you will then have to supply a WHERE condition in your query to restrict the value of n from 1 through 4



    Quote Originally Posted by Ikviens View Post
    Field code belongs to Table many_codes.
    then you should qualify it right in your query with its table name

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    56
    Is it as easy as this? Wow, thanks!
    Code:
    SELECT SUBSTR(code, digits.digit, 1) AS radical
         , digits.digit
         , COUNT(*) subtotal
      FROM many_codes
    INNER
      JOIN digits
     WHERE digits.digit BETWEEN 1 AND 4
       AND SUBSTR(many_codes.code, digits.digit, 1) <> ''
    GROUP
        BY SUBSTR(many_codes.code, digits.digit, 1), digits.digit WITH ROLLUP
    The first section of the WHERE close uses BETWEEN in order "to restrict the value of n from 1 through 4". I hope BETWEEN does not delay execution of the script. Or is it better yet to say digits.digit < 4 and write digits.digit + 1 in SUBSTR sections?
    Last edited by Ikviens; 07-03-11 at 09:57. Reason: messy spacing in SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so does that query produce the correct results?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2006
    Posts
    56
    It gives the right result sets.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    then it's good
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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