Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

count number of instances of certain value 1

Status
Not open for further replies.

dkemas

Programmer
Mar 22, 2012
70
GB
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?
 
Hi

As we warned you, the queries become more and more ugly/unmaintainable/aberrant :
Code:
[b]select[/b]
sum[teal]([/teal]countof1[teal])[/teal] [b]as[/b] total1[teal],[/teal]sum[teal]([/teal]countof1[teal]!=[/teal][purple]0[/purple][teal])[/teal] [b]as[/b] rowwith1[teal],[/teal]
sum[teal]([/teal]countof2[teal])[/teal] [b]as[/b] total2[teal],[/teal]sum[teal]([/teal]countof2[teal]!=[/teal][purple]0[/purple][teal])[/teal] [b]as[/b] rowwith2[teal],[/teal]
sum[teal]([/teal]countof3[teal])[/teal] [b]as[/b] total3[teal],[/teal]sum[teal]([/teal]countof3[teal]!=[/teal][purple]0[/purple][teal])[/teal] [b]as[/b] rowwith3[teal],[/teal]
count[teal](*)[/teal] [b]as[/b] totalrow

[b]from[/b] [teal]([/teal]
  [b]select[/b]
  [teal]([/teal]1score[teal]<=>[/teal][purple]1[/purple][teal])+([/teal]2score[teal]<=>[/teal][purple]1[/purple][teal])+([/teal]3score[teal]<=>[/teal][purple]1[/purple][teal])+([/teal]4score[teal]<=>[/teal][purple]1[/purple][teal])+([/teal]5score[teal]<=>[/teal][purple]1[/purple][teal])+([/teal]6score[teal]<=>[/teal][purple]1[/purple][teal])+([/teal]7score[teal]<=>[/teal][purple]1[/purple][teal])+([/teal]8score[teal]<=>[/teal][purple]1[/purple][teal])+([/teal]9score[teal]<=>[/teal][purple]1[/purple][teal])+([/teal]10score[teal]<=>[/teal][purple]1[/purple][teal])[/teal] [b]as[/b] countof1[teal],[/teal]
  [teal]([/teal]1score[teal]<=>[/teal][purple]2[/purple][teal])+([/teal]2score[teal]<=>[/teal][purple]2[/purple][teal])+([/teal]3score[teal]<=>[/teal][purple]2[/purple][teal])+([/teal]4score[teal]<=>[/teal][purple]2[/purple][teal])+([/teal]5score[teal]<=>[/teal][purple]2[/purple][teal])+([/teal]6score[teal]<=>[/teal][purple]2[/purple][teal])+([/teal]7score[teal]<=>[/teal][purple]2[/purple][teal])+([/teal]8score[teal]<=>[/teal][purple]2[/purple][teal])+([/teal]9score[teal]<=>[/teal][purple]2[/purple][teal])+([/teal]10score[teal]<=>[/teal][purple]2[/purple][teal])[/teal] [b]as[/b] countof2[teal],[/teal]
  [teal]([/teal]1score[teal]<=>[/teal][purple]3[/purple][teal])+([/teal]2score[teal]<=>[/teal][purple]3[/purple][teal])+([/teal]3score[teal]<=>[/teal][purple]3[/purple][teal])+([/teal]4score[teal]<=>[/teal][purple]3[/purple][teal])+([/teal]5score[teal]<=>[/teal][purple]3[/purple][teal])+([/teal]6score[teal]<=>[/teal][purple]3[/purple][teal])+([/teal]7score[teal]<=>[/teal][purple]3[/purple][teal])+([/teal]8score[teal]<=>[/teal][purple]3[/purple][teal])+([/teal]9score[teal]<=>[/teal][purple]3[/purple][teal])+([/teal]10score[teal]<=>[/teal][purple]3[/purple][teal])[/teal] [b]as[/b] countof3

  [b]from[/b] dkemas
[teal])[/teal] foo

Feherke.
 
dkemas, i agree with the warning, both now and in your previous thread

feherke, first time i've seen the null-safe <=> operator in the wild... nice one :)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Thank you Feherke, I am certainly learning lots about mysql very quickly. I received the error

#1248 - Every derived table must have its own alias

but once I gave it an alias it worked perfectly.

I can see that I will be using the null-safe operator quite a few more times for the few remaining queries I have to write.
 
I put AS foo and mysql seemed to like that better!?!?!

One final question, I am trying to join another table to grab the name of the person based on the personalid of the record so am trying

Code:
select

sum(countof1) as total1,sum(countof1!=0) as rowwith1,
sum(countof2) as total2,sum(countof2!=0) as rowwith2,
sum(countof3) as total3,sum(countof3!=0) as rowwith3,

count(*) as totalrow,

PERSONALTABLE.FULLNAME

from (  

select  
(RES.1score<=>1)+(RES.2score<=>1)+(RES.3score<=>1)+(RES.4score<=>1)+(RES.5score<=>1)+(RES.6score<=>1)+(RES.7score<=>1)+(RES.8score<=>1)+(RES.9score<=>1)+(RES.10score<=>1) as countof1,  
(RES.1score<=>2)+(RES.2score<=>2)+(RES.3score<=>2)+(RES.4score<=>2)+(RES.5score<=>2)+(RES.6score<=>2)+(RES.7score<=>2)+(RES.8score<=>2)+(RES.9score<=>2)+(RES.10score<=>2) as countof2,  
(RES.1score<=>3)+(RES.2score<=>3)+(RES.3score<=>3)+(RES.4score<=>3)+(RES.5score<=>3)+(RES.6score<=>3)+(RES.7score<=>3)+(RES.8score<=>3)+(RES.9score<=>3)+(RES.10score<=>3) as countof3  

from RES JOIN PERSONALTABLE ON (RES.PERSONALID = PERSONALTABLE.ID)) AS foo

but I get the error that sql doesn't recognise PERSONALTABLE.FULLNAME

Am I adding the join in the right place?

Thanks
 
Ignore me I have done it, I was putting it in the wrong place and needed to reference the name given at the start of the sql.

:)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top