Problem with the use of count in sql

Question

Problem with the use of count in sql

Navigation

#1 by (2 votes)
#2 by (2 votes)
#3 by (0 votes)
#4 by (0 votes)

1

I have a table with the following data named registers :

--------------------------------------------------------------
  id_plague    |       id_production   |   quadrant
--------------------------------------------------------------
     4         |          3            |     1
--------------------------------------------------------------
     4         |          3            |     2
--------------------------------------------------------------
     4         |          3            |     3
--------------------------------------------------------------
     3         |          3            |     1
--------------------------------------------------------------
     4         |          4            |     1
--------------------------------------------------------------
     4         |          4            |     2
--------------------------------------------------------------
     3         |          4            |     1
--------------------------------------------------------------
     4         |          5            |     1
--------------------------------------------------------------

The table records the pests that were found in a X crop, the crop is divided into beds and each bed is divided into squares. In this case the bed is the id_production and the boxes are the field quadrant .

The table records that the pest was found in each bed frame.

What I need is to count the number of frames that have been registered in the crop, so much so that I can make a simple query like this:

SELECT COUNT(quadrant) AS total
FROM registers;

and it gives me the following result

--------------
    total
--------------
     8
--------------

The problem is that in a table you can register several pests, then save one record per pest per box.

--------------------------------------------------------------
  id_plague    |       id_production   |   quadrant
--------------------------------------------------------------
     4         |          3            |     1
--------------------------------------------------------------
     4         |          3            |     2
--------------------------------------------------------------
     4         |          3            |     3
--------------------------------------------------------------
     3         |          3            |     1
--------------------------------------------------------------

In this table specifically with these records you can see that:

That in the table with value 1 there are two pests with value 4 and 3
In squares with value 2 and 3 they have a plague with value 4

By doing COUNT these records specifically I will give 4 and what I want is that no matter how many pests have a single table I only count it once, that is, in this case to do COUNT I want me to 3 because he found pests in tables 1, 2 and 3

I'm only interested to know how many paintings are infected. I hope you can make me understand with this question hehehehe

mysql sql select

asked by Fabian Sierra 08.09.2017 в 16:11

source

4 answers

2

What you're looking for is this:

SELECT COUNT(DISTINCT quadrant) FROM register

Basically: count the quadrant different in register

answered by 08.09.2017 в 16:58

0

With this you're worth?:

SELECT COUNT(quadrant) AS total
FROM registers
GROUP BY id_production;

I try to group by "productions" which are the "identifiers" you want to distinguish, no?

answered by 08.09.2017 в 16:12

0

Basically you need to count the quadrants you have plague, that's equivalent to counting the different registered quadrants

select count(distinct quadrant) from registers;

If you would like to know how many records you have for serious plague:

select count(quadrant) from registers group by quadrant;

It is always advisable to do count (_uncampo _) not to do count (*) because it consumes more resources.

answered by 08.09.2017 в 17:14

$ http.post Angular POST Php - Ajax Success does not take into account true

score 2 · Accepted Answer

2

I think this is what you want.

SELECT count(*)
  FROM (SELECT quadrant
          FROM registers
          GROUP BY quadrant,id_production) RESULTADO;

the example here

answered by 08.09.2017 / 16:53

source