how do you put da code in da boxers? oooh you highlight it then press block quotes!
“Type faery dragon da wind”
if i put a DISTINCT in at the SELECT bit at da top
SELECT “TrainID”, “Ref No”, distinct “Type faery dragon da wind”, “ForFiltering”, “notes”,
SUM( “ForFiltering”*1.0000 ) OVER ( PARTITION BY “Type faery dragon da wind” ) as “how many”,
(SELECT SUM( “ForFiltering” ) FROM “01_magicCreatures”) as “table total”,
SUM( “ForFiltering”*1.0000 ) OVER ( PARTITION BY “Type faery dragon da wind” )
/
(SELECT SUM( “ForFiltering” ) FROM “01_magicCreatures”) as “Percentage”,
CASE
WHEN “Type faery dragon da wind” = ‘dragon’ THEN ‘01’
WHEN “Type faery dragon da wind” = ‘depth faery’ THEN ‘02’
when “Type faery dragon da wind” = ‘dragon’ THEN ‘03’
when “Type faery dragon da wind” = ‘faery’ THEN ‘04’
when “Type faery dragon da wind” = ‘green faery’ THEN ‘05’
when “Type faery dragon da wind” = ‘half depth faery’ THEN ‘06’
when “Type faery dragon da wind” = ‘mitar dragon’ THEN ‘07’
when “Type faery dragon da wind” = ‘smaller dragon’ THEN ‘08’
when “Type faery dragon da wind” = ‘replace me’ THEN ‘x’
ELSE ‘99’ END as Creature_Type_ID
FROM “01_magicCreatures”
’
I get a ‘isc_dsql_prepare’ error.
same if i try group by in the FROM at the bottom.
SELECT “TrainID”, “Ref No”, “Type faery dragon da wind”, “ForFiltering”, “notes”,
SUM( “ForFiltering”*1.0000 ) OVER ( PARTITION BY “Type faery dragon da wind” ) as “how many”,
(SELECT SUM( “ForFiltering” ) FROM “01_magicCreatures”) as “table total”,
SUM( “ForFiltering”*1.0000 ) OVER ( PARTITION BY “Type faery dragon da wind” )
/
(SELECT SUM( “ForFiltering” ) FROM “01_magicCreatures”) as “Percentage”,
CASE
WHEN “Type faery dragon da wind” = ‘dragon’ THEN ‘01’
WHEN “Type faery dragon da wind” = ‘depth faery’ THEN ‘02’
when “Type faery dragon da wind” = ‘dragon’ THEN ‘03’
when “Type faery dragon da wind” = ‘faery’ THEN ‘04’
when “Type faery dragon da wind” = ‘green faery’ THEN ‘05’
when “Type faery dragon da wind” = ‘half depth faery’ THEN ‘06’
when “Type faery dragon da wind” = ‘mitar dragon’ THEN ‘07’
when “Type faery dragon da wind” = ‘smaller dragon’ THEN ‘08’
when “Type faery dragon da wind” = ‘replace me’ THEN ‘x’
ELSE ‘99’ END as Creature_Type_ID
FROM “01_magicCreatures” group by “Type faery dragon da wind”’
it works fine with just
'>
SELECT “TrainID”, “Ref No”, “Type faery dragon da wind”, “ForFiltering”, “notes”,
SUM( “ForFiltering”*1.0000 ) OVER ( PARTITION BY “Type faery dragon da wind” ) as “how many”,
(SELECT SUM( “ForFiltering” ) FROM “01_magicCreatures”) as “table total”,
SUM( “ForFiltering”*1.0000 ) OVER ( PARTITION BY “Type faery dragon da wind” )
/
(SELECT SUM( “ForFiltering” ) FROM “01_magicCreatures”) as “Percentage”,
CASE
WHEN “Type faery dragon da wind” = ‘dragon’ THEN ‘01’
WHEN “Type faery dragon da wind” = ‘depth faery’ THEN ‘02’
when “Type faery dragon da wind” = ‘dragon’ THEN ‘03’
when “Type faery dragon da wind” = ‘faery’ THEN ‘04’
when “Type faery dragon da wind” = ‘green faery’ THEN ‘05’
when “Type faery dragon da wind” = ‘half depth faery’ THEN ‘06’
when “Type faery dragon da wind” = ‘mitar dragon’ THEN ‘07’
when “Type faery dragon da wind” = ‘smaller dragon’ THEN ‘08’
when “Type faery dragon da wind” = ‘replace me’ THEN ‘x’
ELSE ‘99’ END as Creature_Type_ID
FROM “01_magicCreatures”
’
but then there are 158 individual records for da dragons.