I have also used CTE so that I could generate a "report" just using SQL. Something like (simplified):
WITH summary AS ( SELECT var, count(var) as total FROM table GROUP BY key ) SELECT a.var, 100*a.var/b.total as percentage FROM table AS a INNER JOIN summary AS b ON a.key=b.key ORDER BY key ; ?Yes, I could do the equivalent in the program. But that does make it more complicated. Which makes it easier to make a mistake. And more difficult to modify. Being a bit weird, I have even encapsulated that in a BASH shell script which "transforms" a CSV file (simplified): #!/bin/sh if [ $# -le 3 ]; then echo "At least 3 arguments required." >2 echo "Argument 1 is the name of the input CSV." >2 echo "Argument 2 is the name of the output CSV. " >2 echo "Argument 3 is the name of the \"key\" field." >2 echo "All arguments after than are the names of other variable to include in the output." >2 echo "Note that variables in the input CSV which are not specifically named are excluded." >2 exit 1; fi input="$1" #name of input CSV file shift output="$2" #name of output CSV file shift key="$3" #name of the key field shift varlist="$@" #names of other variables to output, in order # Put in some percentages. sqlite3 <<EOF .mode csv .output "${output}" .import "${input}" test WITH summary AS (select "${key}, count("${key}") as __total, "${varlist}" FROM test GROUP BY "${key}") SELECT a."${key}" 100*a."${key}"/b.total as __percentage FROM test AS a INNER JOIN summary AS b on a."${key}"=b."${key}" ORDER BY "${key}" ; .quit EOF ? Nice and neat. And even easier than PERL or Python.? Hopefully I didn't type something in wrong. -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown