How to count record, with multiple conditions

Question

How to count record, with multiple conditions

Navigation

#1 by (1 votes)

2

I am trying to make a query in MySQL, I have a table that contains the following:

table: id, ano, m1, m2, m3, m4, m5, m6, m7

data: 10, 2016, I, P, L, I, L, P, I

That is, what I want to achieve is that you tell me when it is specifically the ano and the id , that is, the example only has data the ano 2016 and in the following example of query the I do the ano 2015 and it tells me anyway the I in the table calf

query that I make:

SELECT id, COUNT(*) AS r
FROM 'calf' 
WHERE ano='2015' AND id='10' AND m1='I' OR m2='I' OR m3='I' OR m4='I' OR m5='I' OR m6='I' OR m7='I'

mysql mysqli

asked by Alexander Quiroz 19.08.2016 в 15:19

source

1 answer

Register a ModelBinderProvider with Autofac in ASP.NET MVC Because my database file is so big in mongoDB

score 1 · Accepted Answer

The problem is the condition you are using. The select can be interpreted as:

SELECT id, COUNT(*) AS r 
FROM 'calf' 
WHERE 
    (ano ='2015' AND id ='10' AND m1 ='I')
    OR m2 ='I' 
    OR m3 ='I' 
    OR m4 ='I' 
    OR m5 ='I' 
    OR m6 ='I' 
    OR m7 ='I'

That is, if any of the conditions of the m fields is satisfied, it is enough for you to select the record.

Instead, grouping in this way you can solve it:

SELECT id, COUNT(*) AS r 
FROM 'calf' 
WHERE 
    ano ='2015' AND id ='10'
    AND (
       m1 ='I' OR m2 = 'I' OR m3 = 'I' 
       OR m4 = 'I' OR m5 = 'I' OR m6 = 'I' OR m7 = 'I'
    )

Add the I that appear per column, as long as it complies with ano and id :

SELECT 
    sum( m1 = 'I' ) as totalm1,
    sum( m2 = 'I' ) as totalm2,
    sum( m3 = 'I' ) as totalm3,
    sum( m4 = 'I' ) as totalm4,
    sum( m5 = 'I' ) as totalm5,
    sum( m6 = 'I' ) as totalm6,
    sum( m7 = 'I' ) as totalm7


FROM 'calf'

WHERE 
    ano ='2015' AND id ='10'