CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user1698232
user1698232

Reputation: 183

Count query giving wrong column name error

select  COUNT(analysed) from Results where analysed="True"

I want to display count of rows in which analysed value is true.

However, my query gives the error: "The multi-part identifier "Results.runId" could not be bound.".

This is the actual query: 

select ((SELECT COUNT(*) AS 'Count' 
  FROM Results 
  WHERE Analysed = 'True')/failCount) as PercentAnalysed 
from Runs 
where Runs.runId=Analysed.runId

My table schema is:

enter image description here

The Analysed table schema is:

Results table schema

The value I want for a particular runId is: (the number of entries where analysed=true)/failCount

EDIT : How to merge these two queries?

i) select runId,Runs.prodId,prodDate,prodName,buildNumber,totalCount as TotalTestCases,(passCount*100)/(passCount+failCount) as PassPercent, passCount,failCount,runOwner from Runs,Product where Runs.prodId=Product.prodId

ii) select (cast(counts.Count as decimal(10,4)) / cast(failCount as decimal(10,4))) as PercentAnalysed from Runs inner join ( SELECT COUNT(*) AS 'Count', runId FROM Results WHERE Analysed = 'True' GROUP BY runId ) counts on counts.runId = Runs.runId

I tried this : select runId,Runs.prodId,prodDate,prodName,buildNumber,totalCount as TotalTestCases,(passCount*100)/(passCount+failCount) as PassPercent, passCount,failCount,runOwner,counts.runId,(cast(counts.Count as decimal(10,4)) / cast(failCount as decimal(10,4))) as PercentAnalysed from Runs,Product inner join ( SELECT COUNT(*) AS 'Count', runId FROM Results WHERE Analysed = 'True' GROUP BY runId ) counts on counts.runId = Runs.runId
where Runs.prodId=Product.prodId

but it gives error.

Upvotes: 0

Views: 1074

Answers (4)

Hiren Bokarwadiya
Hiren Bokarwadiya

Reputation: 1

select  COUNT(*) from Results where analysed="True"

Upvotes: 0

Esoteric Screen Name
Esoteric Screen Name

Reputation: 6112

Your problems are arising from improper joining of tables. You need information from both Runs and Results, but they aren't combined properly in your query. You have the right idea with a nested subquery, but it's in the wrong spot. You're also referencing the Analysed table in the outer where clause, but it hasn't been included in the from clause.

Try this instead:

select (cast(counts.Count as decimal(10,4)) / cast(failCount as decimal(10,4))) as PercentAnalysed 
from Runs 
inner join
(
  SELECT COUNT(*) AS 'Count', runId 
  FROM Results 
  WHERE Analysed = 'True'
  GROUP BY runId
) counts
on counts.runId = Runs.runId

I've set this up as an inner join to eliminate any runs which don't have analysed results; you can change it to a left join if you want those rows, but will need to add code to handle the null case. I've also added casts to the two numbers, because otherwise the query will perform integer division and truncate any fractional amounts.

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

I'd try the following query:

SELECT COUNT(*) AS 'Count'
FROM Results
WHERE Analysed = 'True'

This will count all of your rows where Analysed is 'True'. This should work if the datatype of your Analysed column is either BIT (Boolean) or STRING(VARCHAR, NVARCHAR).

Upvotes: 1

Sarath Subramanian
Sarath Subramanian

Reputation: 21401

Use CASE in Count

SELECT COUNT(CASE WHEN analysed='True' THEN analysed END) [COUNT]
FROM Results

Upvotes: 0

Related Questions