MYSQL - Show in 2 fields a query to a single field

Question

MYSQL - Show in 2 fields a query to a single field

Navigation

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

1

I have a payment table where in a field called ESTADO_COMPRA shows the following values ( EN CRÉDITO, PENDIENTE DE PAGO ), then I want to count in fictitious fields those that still have credit and those that have already expired without paying. Something like that.

---------------------------------------------------------------------
|Cod_ruc. | nom_empresas.   | Contactados. | EnCredito. | Pendiente |
|-------------------------------------------------------------------|
| 2045621 | LEXCORP ENT.    |           20 |          1 |         2 |
| 2045577 | Wayne Enterpri. |           35 |          3 |         1 |
---------------------------------------------------------------------

The original table is more or less like this

-----------------------------------------------------------------------------
| cod_comp | codi_ruc | nombr_empresa | comprado | tipo_pago | Estado_Venta |
|---------------------------------------------------------------------------|
| C0000001 | 2045621  | LEXCORP ENT.  |      350 |   15 días | EN CREDITO   |
| C0000002 | 2045621  | LEXCORP ENT.  |      150 |   20 días | VENCIDO      |
| C0000003 | 2045621  | LEXCORP ENT.  |     1150 |   15 días | VENCIDO      |
| C0000004 | 2045577  | WAYNE ENTPR.  |     3520 |   30 días | EN CREDITO   |
| C0000005 | 2045577  | WAYNE ENTPR.  |     4010 |   30 días | PAGADO       |
| C0000006 | 2045577  | WAYNE ENTPR.  |     5050 |   30 días | EN CREDITO   |
| C0000007 | 2045577  | WAYNE ENTPR.  |     2050 |   30 días | VENCIDO      |
| C0000008 | 2045577  | WAYNE ENTPR.  |     2150 |   30 días | EN CREDITO   |
-----------------------------------------------------------------------------

I complicate in dividing the count of Estado_Venta into 2 fields, En crédito and Pendiente . * I forgot to mention that the contacted field comes from a JOIN from the CustomerCalls table (contacted, cod_ruc, detail_called), here simply with a Count (cod_ruc) I can count the number of contacts a specific client has had.

mysql

asked by neojosh2 23.08.2017 в 20:02

source

2 answers

How do I implement Passport in angular 2.0 and nodeJs? Use result of SELECT as table name for another SELECT

score 1 · Answer 1

It can be resolved in the following way:

select  p.codi_ruc,
    p.nombr_empresa,
    l.contactados                                                       AS 'Contactados.',
    COUNT(CASE WHEN p.Estado_Venta = 'EN CREDITO' THEN 1 ELSE NULL END) AS 'EnCredito.',
    COUNT(CASE WHEN p.Estado_Venta = 'VENCIDO' THEN 1 ELSE NULL END)    AS 'Pendiente '
    from pagos p
    inner join (select cod_ruc, 
                       Count(l.cod_ruc) as contactados
                       from LlamadasClientes
                       group by cod_ruc
        ) l
        on p.codi_ruc = l.cod_ruc
    group by p.codi_ruc,
         p.nombr_empresa, 
         l.contactados

Obviously I could not prove it, and I do not know if the pagos table is actually called like that, but the idea is clear, we use count only for the conditions we need, if I remember correctly in MySQL the count(null) does not count, but could be replaced by a sum .

score 0 · Answer 2

If your field you want to separate into 2 has this format (value1, value2) you can make a view where you add all the data you want to show except the one you want to split and then add this to your view

SELECT
tabla.id,
tabla.campo1,
tabla.campo2,
select TRIM(SUBSTR(campo3,2,INSTR(campo3,",")-2)) as subcampo1,
TRIM(SUBSTR("campo3,INSTR(campo3,",")+2,INSTR(campo3,")")-INSTR("campo3",",")-2)) as subcampo2
FROM
mitabla

The fields that you would keep would be id, field1, and field3. Later I identify field3 as the field that has the values that I am going to separate, later what I do is to separate both values in two different fields called subfield1 and subfield2 in your case you would call it In Credit and Pending To divide them, I just looked in the chain for the delimiter that separated both values, which in this case was the comma and started taking the first value from the beginning, without counting the ( through , and the second value from , until the end without counting the ) , if this is not the format you use, you can explain it better to give you the exact solution of all I would only change very little of the answer I give you.