Group consultation without losing separate records 2

Question

Group consultation without losing separate records 2

Navigation

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

-1

Hi, I have the same problem as in this question ( Group query without losing different records ), it's complicated explain it.

When the results of the records are grouped, information is lost, for example I have data in the customer table and in the sales table, I need to know on which date the sale was made to said customer, so if I want to add the sales for each client would be used group by ventas.id_cliente , but in that grouping the dates are lost because they are for sale, when grouping by ventas.id_venta then if the dates that I need to know are visualized but what I really need is that when grouping it by ventas.id_cliente , in term of 30 days if that customer bought something, then in a term of 60 days the sales of that client and in 90 days the sales of that client, grouped by ventas.id_cliente , I can not determine if there were sales 30 days ago because the sales date of 30 days ago is lost in the grouping by id_cliente . So the question is, how to avoid losing that data? PS: Ok, I'll try to explain it better: here I put an image of the tables grouped by id_venta

They are two records because that client has two sales, one sale on each date respectively, the fields shown with 0_30 refer to if that sale is 30 days old, the field 30_60 if the sales have 60 days of antiquity, and so on. But when I group it by clients, the record of the most current date disappears and I need you to tell it so that the result is like this: This is grouped by client_id

But what I get is this:

In this case, it is as if it does not take into account the record of the most current date, when grouped by client_id it is lost.

mysql

asked by Schismdark 26.07.2018 в 01:16

source

2 answers

Relate Data for inventory Exception when calling a Thread from another class

score 0 · Answer 1

Counting the total of each sale of that client, for each record of the select:

 select (select Count(id_cliente) 
              from ventas 
                  where ventas.id_cliente = v1.id_cliente
            ) _ventas_por_cliente 
         , v1.*
    from ventas v1

O By making an inner join of the sales table and a select with the totals This being the least unnecessary work done.

 select v2.*, v1.*
    from ventas v1 INNER JOIN (select Count(*) as _total,id_cliente
              from ventas 
                  group by id_cliente
            ) v2 ON v1.id_cliente = v2.id_cliente

score 0 · Answer 2

I will try to give you a solution ...

We need to assemble a table, return that data as you want .. and then group ... then we will do the following ...

select 
id_venta,
id_cliente,
(CASE
    WHEN DATEDIFF(fechaventa,NOw()) < 31 THEN valorCobro
    else 0) as 0_30,
(CASE
    WHEN DATEDIFF(fechaventa,NOw()) between (31,60) THEN valorCobro
    else 0) as 31_60,
(CASE
    WHEN DATEDIFF(fechaventa,NOw()) between (61,90) THEN valorCobro
    else 0) as 61_90,
(CASE
    WHEN DATEDIFF(fechaventa,NOw()) > 90 THEN valorCobro
    else 0) as 90_mas,
from Tabla

And then, this table could be the entry of another table that only makes the additions

select
     id_cliente,
     sum(0_30),
     sum(31_60),
     sum(61_90),
     sum(90_mas)
from 
    (la tabla anterior)
gropu by id_cliente