How do I count the number of items in a field repeated?

Question

How do I count the number of items in a field repeated?

Navigation

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

0

I have a table with the field CtrlChargeNum that has the log of the number of the load of a truck. I need to make a query that brings the total of that field. Until I got the query, see:

SELECT COUNT (*) CtrlCargaNum 
FROM CTRL_CARGA 
WHERE CtrlCargaData BETWEEN '2018-01-01' AND '2018-01-04' AND EmpCod = '01.01'

But it only counts the records that are on these dates.

I would like it to take the number of the load that is repeated and count as 1 LOAD

Ex: 
CtrlCargaNum = 1001
CtrlCargaNum = 1001

 = 1 CARGA

CtrlCargaNum = 1002
CtrlCargaNum = 1002
CtrlCargaNum = 1002

 = 1 CARGA

and add these loads: 1 LOAD + 1 LOAD = 2. I am using mysql server

php mysql sql sql-server delphi-7

asked by anonymous 15.03.2018 / 20:48

2 answers

Presentation of the chart on page XML signature error for EFD-Reinf

score 1 · Answer 1

You could do something like this:

SELECT COUNT(c.CtrlCargaNum) CtrlCargaNum 
FROM (SELECT DISTINCT CtrlCargaNum FROM CTRL_CARGA WHERE CtrlCargaData BETWEEN 
'2018-01-01' AND '2018-01-04' AND EmpCod = '01.01') c

score 0 · Answer 2

The MySQL and SQL Server tags are checked. Here is the solution for SQL Server, but the use of DISTINCT is also valid for MySQL.

-- código #1
SELECT count(distinct CtrlCargaNum) as Qtd
  from CTRL_CARGA 
  where CtrlCargaData between '2018-01-01' and '2018-01-04'
        and EmpCod = '01.01';