SQL query in MySQL between 3 sum and count tables

Question

SQL query in MySQL between 3 sum and count tables

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (-1 votes)

1

I have 3 tables. One is clientes , other fotocopias and recargas_saldo , related by id_cliente which is primary key in clientes and foránea in the others.

I need to take out the following:

1 .- Nombre, apellidos of the table clientes .

2.- The sum of all the copies made by the client that are in cantidad of the table fotocopias .

3.- The sum of all the recharges made by the client that are in cantidad_recargada of the table recargas_saldo .

The problem is that if I put sum and count I get very rare values and if I do not put it I see that I duplicate the fields ... I've tried several sentences and nothing ...

CREATE TABLE 'clientes' (
  'id_cliente' int(10) NOT NULL,
  'nombre' varchar(50) COLLATE utf8_spanish_ci NOT NULL,
  'apellidos' varchar(50) COLLATE utf8_spanish_ci NOT NULL,
  'email' varchar(100) COLLATE utf8_spanish_ci NOT NULL,
  'dni' varchar(10) COLLATE utf8_spanish_ci NOT NULL,
  'telefono' varchar(15) COLLATE utf8_spanish_ci NOT NULL,
  'saldo' decimal(5,2) NOT NULL,
  'fecha_alta' datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

CREATE TABLE 'fotocopias' (
  'id_fotocopia' int(10) NOT NULL,
  'id_cliente' int(10) NOT NULL,
  'tipo_fotocopia' varchar(20) COLLATE utf8_spanish_ci DEFAULT NULL,
  'cantidad' int(10) DEFAULT NULL,
  'precio' decimal(5,2) DEFAULT NULL,
  'fecha_copias' datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

CREATE TABLE 'fotocopias_saldo' (
  'id_registro' int(10) NOT NULL,
  'id_cliente' int(10) NOT NULL,
  'nombre_cliente' varchar(50) COLLATE utf8_spanish_ci DEFAULT NULL,
  'apellido_cliente' varchar(50) COLLATE utf8_spanish_ci DEFAULT NULL,
  'saldo_old' decimal(5,2) DEFAULT NULL,
  'saldo_new' decimal(5,2) DEFAULT NULL,
  'fecha_reg' datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

Indices de la tabla 'clientes'
--

ALTER TABLE 'clientes'
  ADD PRIMARY KEY ('id_cliente');

--
-- Indices de la tabla 'fotocopias'
--
ALTER TABLE 'fotocopias'
  ADD PRIMARY KEY ('id_fotocopia'),
  ADD KEY 'id_cliente' ('id_cliente');

--
-- Indices de la tabla 'recarga_saldo'
--
ALTER TABLE 'recarga_saldo'
  ADD PRIMARY KEY ('id_recarga'),
  ADD KEY 'id_cliente' ('id_cliente');

I've tried:

select 'clientes'.'nombre' AS 'nombre','clientes'.'apellidos' AS 'apellidos',sum('fotocopias'.'cantidad') AS 'total_copias',count('recarga_saldo'.'id_cliente') AS 'recargas',sum('recarga_saldo'.'cantidad_recargada') AS 'total_recargado' from (('clientes' join 'fotocopias') join 'recarga_saldo') where (('clientes'.'id_cliente' = 'fotocopias'.'id_cliente') = 'recarga_saldo'.'id_cliente') group by 'clientes'.'id_cliente' ;

Y:

SELECT clientes.nombre, clientes.apellidos, sum(fotocopias.cantidad), count(recarga_saldo.id_cliente), sum(recarga_saldo.cantidad_recargada 
FROM CLIENTES
WHERE 
clientes.id_cliente = recargas_saldo.id_cliente = fotocopias.id_cliente
GROUP BY clientes.id_cliente

But it does not add well or does not take the values well, the truth is that I'm pretty rusty ...: (

mysql sql phpmyadmin consulta

asked by Alex 17.10.2018 в 13:16

source

3 answers

Update does not update table Dictionaries within a dictionary, python 3

score 2 · Answer 1

You can do this by joining the tables with JOIN and at the same time grouping by clients with GROUP BY .

For example:

SELECT
    c.nombre,
    c.apellidos, 
    SUM(f.cantidad) copias,
    SUM(r.cantidad_recargada) recargas
FROM clientes c
INNER JOIN fotocopias    f ON c.id_cliente = f.id_cliente
INNER JOIN recarga_saldo r ON c.id_cliente = r.id_cliente
GROUP BY c.id_cliente;

Notes:

Sometimes the queries do not show the expected data because there is really an error in the same data
In your CREATE TABLE you can see that you have not created foreign keys in the tables, which will affect the integrity and performance of them. You should create foreign keys
It is unclear what the% fotocopias_saldo paints. It looks like a table that handles redundant data
It is likely that the query has to standardize with respect to the columns nombre and apellidos . You can do this by putting them together in an aggregation function like GROUP_CONCAT or separated with a function like MAX or MIN .

score 1 · Answer 2

It can be done with separate subqueries (probably not the most optimal way)

The first subquery will bring the data of the copies

SELECT id_cliente, sum(cantidad) FROM TABLA_FOTOCOPIAS GROUP BY id_cliente

The second consultation will do the same as the refills

SELECT id_cliente sum(cantidad_recargada) FROM TABLA_RECARGAS_SALDO GROUP BY id_cliente

Now what we have to do is join the subqueries in another query.

SELECT T1.id_cliente, T1.fotocopias, T2.recargas 
FROM 
    (SELECT id_cliente, sum(cantidad) as fotocopias FROM TABLA_FOTOCOPIAS GROUP BY id_cliente) AS T1, 
    (SELECT id_cliente sum(cantidad_recargada) as recargas FROM TABLA_RECARGAS_SALDO GROUP BY id_cliente) AS T2
WHERE T1.id_cliente = T2.id_cliente;

score -1 · Answer 3

try the following (generate a subquery in the query to your CLIENTS table, in which each subquery searches for the sum of the data in photocopies and in reloads) ... With that you will obtain all your list of clients, and values in 0 if they have not made photocopies (or recharges), or the total value of them. If you are interested you can generate a VISTA in the Database to always have the data at your disposal without having to calculate them (only doing select * from NAME_of_THE_VISTA)

SELECT  c.nombre,
c.apellidos, 
(select SUM(f.cantidad) from fotocopias f where c.id_cliente = f.id_cliente) copias,
(select SUM(r.cantidad_recargada) from recargas r where c.id_cliente = r.id_cliente) recargas FROM clientes c