Results 1 to 6 of 6
Thread: Two Aggregation Necessary?

070311, 06:15 #1Registered User
 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;
Field code belongs to Table many_codes.

070311, 06:57 #2SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
use WITH ROLLUP on your (single) GROUP BY
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),... ;
then you should qualify it right in your query with its table name

070311, 09:47 #3Registered User
 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
Last edited by Ikviens; 070311 at 09:57. Reason: messy spacing in SQL

070311, 10:27 #4SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
so does that query produce the correct results?

070311, 10:43 #5Registered User
 Join Date
 Mar 2006
 Posts
 56
It gives the right result sets.

070311, 11:23 #6SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
then it's good