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
