Show records that are repeated every month

-4

I have been working on a table in a database, which I need to show the clients that make at least one purchase every month. But until now I could not.

I leave the table and the query as it should appear, so they can help me.

PURCHASES table:

CLIENTES  MES_COMPRA
--------  ----------------
ANGEL     SEPTIEMBRE
BLANCA    SEPTIEMBRE
DIEGO     SEPTIEMBRE
FERNANDO  OCTUBRE
DIEGO     OCTUBRE
GABRIEL   OCTUBRE
BLANCA    OCTUBRE

Expected result of the query:

CLIENTES
--------
DIEGO   
BLANCA  
    
asked by Parzival 23.10.2018 в 20:35
source

4 answers

1

You can solve it in this way with a subquery

SUBCONSULTA

SELECT cliente
    FROM compras
    WHERE cliente
    IN (
    SELECT cliente
    FROM compras
    GROUP BY cliente
    HAVING count( cliente ) >1
    )
    GROUP BY cliente
    ORDER BY cliente

What I did was

  • I select the column cliente
  • I ask you to select cliente where cliente is inside a subquery
  • Within the subquery, I select cliente and group the results by that same column
  • It indicates with having that I do the previous actions when I have a count of the column% co_of% greater than 1
  • Out of the subquery I group the results
  • I finally order the results
  • CTE

    If you use mysql from version 8, you can use cliente and your query would be as follows

    WITH consulta AS(
       SELECT cliente FROM compras
       GROUP BY cliente
       HAVING COUNT(cliente) > 1
    )
     SELECT cliente FROM consulta WHERE cliente IN(SELECT * FROM consulta);
    

    What I did was

      
  • Inside a query stored with an alias called CTE I made a consulta where I group the results provided that the SELECT   of customer is greater than 1
  •   
  • On the outside I do a conteo but not the table if not the SELECT where the CTE is in a query to the cliente query
  •   

    Here you have a link with both examples to visualize and see if they serve you

        
    answered by 24.10.2018 / 02:09
    source
    1

    If what you need to know is the customers who buy every month, you could start with the following analysis. For the customer to buy all months means that you must have at least one record per month (I must group per client and per month) after that know how many months I have managed (suppose 12).

    SELECT cliente, count(*) -- Agrupo a nivel de cliente
    FROM (
        SELECT clientes, mes_compra, count(*) -- Agrupo a nivel de cliente y sus compras por mes
        FROM COMPRAS
    ) AS X
    HAVING count(*) = 12 -- El comparativo de los 12 meses del año
    

    Note: The 12 months is the number established by you, depending on the months managed (in this case 12), if this value is different you must change it, if it needs to be dynamic you should support it in a subquery.

        
    answered by 24.10.2018 в 00:14
    1
    SELECT
       clientes
    FROM
       compras
    GROUP BY
       clientes
    HAVING
       COUNT (mes_compra) =
          (SELECT COUNT(mes_compra) FROM compras)
    ORDER BY
       clientes
    

    I have not checked this answer, and may have errors.

    The concept is that the subquery calculates the number of different months in the entire table. Each group also has a number of months. When they are equal, the group contains all the months, and contributes a value of clients.

        
    answered by 24.10.2018 в 14:36
    1

    It all depends ... maybe something like this will help you, you should keep in mind that the query will drop the customers who register purchases in the months burned in the where, so put only those you need

    SELECT 
      CLIENTES 
    FROM 
      COMPRAS 
    WHERE 
      MES_COMPRA = 'ENERO' AND
      MES_COMPRA = 'FEBRERO' AND 
      MES_COMPRA = 'MARZO' AND 
      MES_COMPRA = 'ABRIL' AND 
      MES_COMPRA = 'MAYO' AND 
      MES_COMPRA = 'JUNIO' AND 
      MES_COMPRA = 'JULIO' AND 
      MES_COMPRA = 'AGOSTO' AND 
      MES_COMPRA = 'SEPTIEMBRE' AND 
      MES_COMPRA = 'OCTUBRE' AND 
      MES_COMPRA = 'NOVIEMBRE' AND 
      MES_COMPRA = 'DICIEMBRE';
    
        
    answered by 24.10.2018 в 01:01