count not rows but instances in each row 1

[dkemas]

WE have a database with a few tables, one of them features the columns

id | 1score | 1seen | 1pdp | 1red | 2score | 2seen | 2pdp | 2red | 3score | 3seen | 3pdp | 3red | 4score | 4seen | 4pdp | 4red | 5score | 5seen | 5pdp | 5red | 6score | 6seen | 6pdp | 6red | 7score | 7seen | 7pdp | 7red | 8score | 8seen | 8pdp | 8red | 9score | 9seen | 9pdp | 9red | 10score | 10seen | 10pdp | 10red

I need to create a SQL query that will go through each row and count the number of *red it finds.

So for example if one rown has 1red, 4red and 8red set to 1 (NULL is default) then the sql will say there are

FOUND 3 items in 1 record
if it searches 10 records and finds 15 instances of *red it will display
FOUND 15 items in 10 records

Is this possible with that database table structure?

I can find out the number of records that features a red set to 1, but not the total instances. To find the number of records I am using

SELECT count(*) 
FROM myTable
WHERE 1red = '1'
OR 2red = '1'
OR 3red = '1'
OR 4red = '1'
OR 5red = '1'
OR 6red = '1'
OR 7red = '1'
OR 8red = '1'
OR 9red = '1'
OR 10red = '1'

 

[feherke]

Hi

having that many repetitive columns is suggestive of poor database
normalisation.

( See Justin's post in thread434-1681613 at 26 Apr 12 10:20. )

Any further development on that database will be a pain. Better drop it all and start over before going too far.

Your question can be answered like this, but this leads to nothing good :

[b]SELECT[/b] count[teal](*),[/teal]
sum[teal]([/teal]coalesce[teal]([/teal]1red[teal],[/teal][purple]0[/purple][teal])+[/teal]coalesce[teal]([/teal]2red[teal],[/teal][purple]0[/purple][teal])+[/teal]coalesce[teal]([/teal]3red[teal],[/teal][purple]0[/purple][teal])+[/teal]coalesce[teal]([/teal]4red[teal],[/teal][purple]0[/purple][teal])+[/teal]coalesce[teal]([/teal]5red[teal],[/teal][purple]0[/purple][teal])+[/teal]coalesce[teal]([/teal]6red[teal],[/teal][purple]0[/purple][teal])+[/teal]coalesce[teal]([/teal]7red[teal],[/teal][purple]0[/purple][teal])+[/teal]coalesce[teal]([/teal]8red[teal],[/teal][purple]0[/purple][teal])+[/teal]coalesce[teal]([/teal]9red[teal],[/teal][purple]0[/purple][teal])+[/teal]coalesce[teal]([/teal]10red[teal],[/teal][purple]0[/purple][teal]))[/teal]
[b]FROM[/b] myTable
[b]WHERE[/b] 1red [teal]=[/teal] [green][i]'1'[/i][/green]
[b]OR[/b] 2red [teal]=[/teal] [green][i]'1'[/i][/green]
[b]OR[/b] 3red [teal]=[/teal] [green][i]'1'[/i][/green]
[b]OR[/b] 4red [teal]=[/teal] [green][i]'1'[/i][/green]
[b]OR[/b] 5red [teal]=[/teal] [green][i]'1'[/i][/green]
[b]OR[/b] 6red [teal]=[/teal] [green][i]'1'[/i][/green]
[b]OR[/b] 7red [teal]=[/teal] [green][i]'1'[/i][/green]
[b]OR[/b] 8red [teal]=[/teal] [green][i]'1'[/i][/green]
[b]OR[/b] 9red [teal]=[/teal] [green][i]'1'[/i][/green]
[b]OR[/b] 10red [teal]=[/teal] [green][i]'1'[/i][/green]

Feherke.

http://feherke.github.com/

 

[dkemas]

Thanks again Feherke, you are saving me big time at the moment. I have been royally thrown in the deep end with this project so you help is very much appreciated

I am aware that a database of this type isn't ideal but I think it's the only way to go for what they want to achieve (which is constantly changing). Someone of your abilities may well take one look at it all and know a much better way but the budget was so small on this that they couldn't afford a developer.