Two Aggregation Necessary?

Ikviens · #1 (**permalink**) 07-03-11, 05:15

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.

r937 · #2 (**permalink**) 07-03-11, 05:57

Quote:

Originally Posted by Ikviens

... 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

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

Field code belongs to Table many_codes.

then you should qualify it right in your query with its table name

Ikviens · #3 (**permalink**) 07-03-11, 08:47

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?

r937 · #4 (**permalink**) 07-03-11, 09:27

so does that query produce the correct results?

Ikviens · #5 (**permalink**) 07-03-11, 09:43

It gives the right result sets.

r937 · #6 (**permalink**) 07-03-11, 10:23

then it's good

Internet Services

Web Development

Digital Marketing

Consumer Tech