How to count the number of clients that returned from one year to the next through SQL?

Question

I have a table Serviços(Nro_Serv, Data, Cliente, Valor) and would like to get customers who did service with me in a year X (ex: 2011) and who also did in a year Y (ex: 2012). I tried to use COUNT(*) and then GROUP BY Cliente HAVING COUNT(*) > 1. But you’re not giving a real value.

Code of SQL:

SELECT cliente.nmcliente, COUNT(*) 
FROM ordemservico 
INNER JOIN cliente 
   ON ordemservico.cdcliente = cliente.cdcliente
WHERE ordemservico.auxordemservico = '/12' 
   OR ordemservico.auxordemservico = '/13'
GROUP BY cliente.nmcliente 
HAVING COUNT(*) > 1

The auxordemservico field is a field with year suffix.

Editing I changed the SQL and I did the following:

SELECT cliente.nmcliente, ordemservico.auxordemservico, COUNT(*)
FROM ordemservico 
INNER JOIN cliente 
   ON ordemservico.cdcliente = cliente.cdcliente
WHERE ordemservico.auxordemservico = '/12' 
   OR ordemservico.auxordemservico = '/13'
GROUP BY cliente.nmcliente, ordemservico.auxordemservico
HAVING COUNT(*) > 1

Now he returns to me clearly all customers who did services in the two years. I need to take out the ones that are not duplicated first, because I care who did services in the two years. And after that apply a "Distinct".

Answer 1

Adapt this script to your table fields.

The return is just the idClient without the repetitions.

select o.idCliente from ordem as o where o.idCliente IN (SELECT o.idCliente from cliente as c inner join ordem as o ON c.idcliente = o.idCliente where o.ano = '2013') and o.ano = '2012' group by o.idcliente;

Answer 2

I managed to solve the problem as follows:

SELECT * FROM(
    SELECT DISTINCT(os1.cdcliente)
    FROM ordemservico os1
    WHERE os1.auxordemservico = '/12'
        AND EXISTS (
            SELECT os1.cdcliente
            FROM ordemservico os2
            WHERE os2.auxordemservico = '/13'
                AND os1.cdcliente = os2.cdcliente
        )
) AS temp_table

It searches line by line all "/12" records that are also in "/13"

Answer 3

To achieve this result numerous solutions can be employed, one of them follows.

Suppose there is the following data scheme:

CREATE TABLE Servicos
(
  Nro_Serv int primary key,
  Data date,
  Cliente int,
  Valor float
);

INSERT INTO Servicos
(Nro_Serv, Data, Cliente, Valor)
VALUES
(1, '2011-03-01', 200, 53.66),
(2, '2011-03-05', 200, 108.90),
(3, '2012-01-25', 200, 85.45),
(4, '2012-02-09', 300, 785.45),
(5, '2013-11-09', 300, 77.45),
(6, '2014-01-22', 400, 87.45),
(7, '2011-03-05', 300, 778.90);

Based on this scheme we can employ the following query:

select cliente 
from Servicos 
where YEAR(data) = '2011' 
group by cliente
UNION
select cliente 
from Servicos 
where YEAR(data) = '2012' 
group by cliente

Basically there are two queries, each grouping per client and filtering per year. So one of them is for the year 2011 and the other for the year 2012 which at the end are united using the operator UNION, which results in what is expected.

To test the above proposal, see all on sqlfiddle.

Answer 4

Here’s my contribution. I took tests and it worked.

create table ORDEMSERVICO
(
    CDCLIENTE int,
    AUXORDEMSERVICO varchar(3)
);

insert into  ORDEMSERVICO values (12, '/12');
insert into  ORDEMSERVICO values (13, '/13');
insert into  ORDEMSERVICO values (25, '/12');
insert into  ORDEMSERVICO values (25, '/13');

select
  CDCLIENTE,
  sum(case when AUXORDEMSERVICO = '/12' then 1 else 0 end) as Ordem2012,
  sum(case when AUXORDEMSERVICO = '/13' then 1 else 0 end) as Ordem2013
from
  ORDEMSERVICO
group by 
  CDCLIENTE
having
  sum(case when AUXORDEMSERVICO = '/12' then 1 else 0 end) > 0 and
  sum(case when AUXORDEMSERVICO = '/13' then 1 else 0 end) > 0

Something screams in there... Simulated here:

http://sqlfiddle.com/#! 3/4840d0/1

Answer 5

You need to search by year using the YEAR which serves to extract the year in string format for you to compare, and give a group by in Client for example:

SELECT count(*) FROM Servicos WHERE YEAR(Data)='2012' OR YEAR(Data)='2011' GROUP BY CLiente

this select was made based on the data you gave us..

Answer 6

Sorry my mistake. Try:

SELECT cliente.nmcliente 
FROM cliente 
INNER JOIN (SELECT o1.cdcliente FROM ordemservico o1 WHERE o1.auxordemservico = '/12') AS r1
   ON r1.cdcliente = cliente.cdcliente
INNER JOIN (SELECT o1.cdcliente FROM ordemservico o1 WHERE o1.auxordemservico = '/13') AS r2
   ON r2.cdcliente = cliente.cdcliente

Due to INNER JOIN will only select those that exist in the 2 years.

Depending on your DBMS you can use INTERSECT:

SELECT cliente.nmcliente
FROM cliente 
INNER JOIN 
    ((SELECT cdcliente FROM ordemservico WHERE auxordemservico = '/12')
        INTERSECT
    (SELECT cdcliente FROM ordemservico WHERE auxordemservico = '/13')) AS r
   ON cliente.cdcliente = r.cdcliente)

See 3 options in this test in SQL Fiddle:
http://sqlfiddle.com/#! 15/c6186/11