DOUBT SELECT WITH INNER JOIN AND COUNT

Question

Hello, I am relatively new when the subject and database and need to generate a report with the following information,:

Client name

Qty of purchases made by each customer

Total in R$ of each customer’s purchases

Qty of purchases made in the current year of each customer

Total in R$ of purchases in the current year of each customer

Profile photo (photo with ordem_photo = 1)

considering only purchases with the status (sel_status_purchase) completed

with the following tables

table clientes_photos:

table purchases above

itenspurchase table above

table customers above

Answer 1

I didn’t see any date field to filter the date of purchases by year, but I tried to bring the query as close as possible. Try it on your computer and adjust what you need.

select 
    cli.nome_cliente, 
    count(c.id_compra), 
    sum(ic.valor_itemc),
    count(sra.id), 
    sum(sra.valor),
    cf.url_foto
from cliente cli
    inner join compras c on cli.id_cliente = c.fk_id_cliente_clientes
    inner join itenscompra ic on ic.fk_id_compra = c.id_compra
    left join (select * from clientes_fotos where ordem_foto = 1) cf on cf.fk_id_cliente_clientes = cli.id_cliente
    left join (select compras.fk_id_clientes_clientes, compras.id_compra, sum(itenscompra.valor_itemc) as valor
                from compras 
                    inner join itenscompra on itenscompra.fk_id_compra = compras.id_compra
                where year(compras.**ALGUM CAMPO DATA NA TABELA COMPRAS**)=2019 and compras.sel_status_compra = "Concluido"
                group by compras.id_compra) sra on cli.id_cliente = sra.fk_id_cliente_clientes
where
    c.sel_status_compra = "Concluido"
group by cli.nome_cliente, cf.url_foto