Problem in consultation with Join

Asked

Viewed 76 times

1

I’m having a little problem with the consultation to generate a report. I made a JOIN of the orders table (that will take the report) with the table of customers and products. Until then everything worked, but it hardly shows all the requests, only shows the first. At the moment I have about 3 requests related to the tables. See the code:

SELECT pedidos_id,
       pedidos.cliente_id,
       pedidos.produto_id,
       clientes.nome AS nome_cliente,
       produtos.nome AS nome_produto,
       pedidos.data,
       pedidos.frete,
       pedidos.quantidade,
       pedidos.total,
       SUM(pedidos.total) as sub_total
  FROM pedidos
       INNER JOIN clientes ON pedidos.cliente_id = clientes.clientes_id
       INNER JOIN produtos ON pedidos.produto_id = produtos.produtos_id
 ORDER BY clientes.nome

I wonder what’s wrong with the code? inserir a descrição da imagem aqui

php mysql sql sql-server postgresql

  • Missed the GROUP BY pedidos_id, nay?

  • Dude it even works but the problem is the sub-total. It does not sum the values of the total at all.

  • You need to be a little clearer on your question. What data do you expect at the end? How do you intend to get the subtotal?

  • The sub-total has to be from the total column. See I have 3 records and I need him to make the relationship with the other two tables of customers and products and each order has a total. The sub-total would be the sum of all requests in the total column.

  • Dude.... if you want to make a report, the total is summed up in the report itself and not in select... what are you using to make the report ?

2 answers

2

Check that you have columns with different values... Obs: fields in YELLOW

inserir a descrição da imagem aqui you cannot use an aggregation function SUM with different values on lines.

Note: In this case the column would be deleted SUM and would make the summation inside the report.

1

Make a Group by of all non-aggregated fields, the way it is, it’s summing all the orders into a single record: 

SELECT pedidos_id  
     , pedidos.cliente_id  
     , pedidos.produto_id  
     , clientes.nome AS nome_cliente  
     , produtos.nome AS nome_produto  
     , pedidos.data  
     , pedidos.frete  
     , pedidos.quantidade  
     , pedidos.total  
     , SUM(pedidos.total) as sub_total  
  FROM pedidos  
 INNER JOIN clientes ON pedidos.cliente_id = clientes.clientes_id  
 INNER JOIN produtos ON pedidos.produto_id = produtos.produtos_id  
 GROUP BY pedidos_id  
        , pedidos.cliente_id  
        , pedidos.produto_id  
        , clientes.nome
        , produtos.nome
        , pedidos.data  
        , pedidos.frete  
        , pedidos.quantidade  
        , pedidos.total  
 ORDER BY clientes.nome

  • It worked but I need to calculate the total column of all records.

  • Which bank are you using?

  • mysql Workbench

  • I edited the question

  • Try to make use of WITH ROLLUP right behind the GROUP BY statement. It would GROUP BY PEDIDOS.PEDIDOS_ID WITH ROOLUP, or if you want in a more traditional way, only with subSelect.

Browser other questions tagged php mysql sql sql-server postgresql

You are not signed in. Login or sign up in order to post.