I have managed to create many scripts to solve our needs but this one is eluding me.
Juat to recap, my table structure is
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
The *score fields are either NULL, 1 or 2 or 3.
I need to create a query that will present results in the format
There are ? values of 3 in ? rows containing at least one value of 3 out of 10 total rows
There are ? values of 2 in ? rows containing at least one value of 2 out of 10 total rows
There are ? values of 1 in ? rows containing at least one value of 1 out of 10 total rows
So in essence I need to
Count the number of rows in total (select count(*) from mytable)
Count the number of rows with a value of 3 in any of the *count columns
Then look at all *count in all rows and count how many it finds
the latter two for the values of 2 and 1 in the *count rows.
I am going around in circles with this, is this requirement a step too far for SQL?
Juat to recap, my table structure is
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
The *score fields are either NULL, 1 or 2 or 3.
I need to create a query that will present results in the format
There are ? values of 3 in ? rows containing at least one value of 3 out of 10 total rows
There are ? values of 2 in ? rows containing at least one value of 2 out of 10 total rows
There are ? values of 1 in ? rows containing at least one value of 1 out of 10 total rows
So in essence I need to
Count the number of rows in total (select count(*) from mytable)
Count the number of rows with a value of 3 in any of the *count columns
Then look at all *count in all rows and count how many it finds
the latter two for the values of 2 and 1 in the *count rows.
I am going around in circles with this, is this requirement a step too far for SQL?