Query with more than one view field

Asked

Viewed 69 times

1

I have doubts about how to perform the query displaying two fields, with only one. I have the Clients Table (id, name, date of birth) and the Transactions table (id_trans, id_clien, valor_trans). I need the query to show the total spent per client with the fields: nome_clie, id_clie, total_trans).

When I try to add a second column to display the error, it only works with name and total spent.

This is my query that works, I need it to also display the client ID, only the name appears.

select
  nm_cli,
  sum(vl_trn) as tot_trn
from
  tb_cli left join tb_trn on (tb_cli.id_cli = tb_trn.id_cli)
group by nm_cli;
mysql sql query

  • What is the error that gives and which SQL was executed?

  • Error Code: 1052. Column 'id_cli' in field list is ambiguous

  • select nm_cli,
sum(vl_trn) as tot_trn
from tb_cli
 left join tb_trn
 on (tb_cli.id_cli = tb_trn.id_cli)
 group by nm_cli;

2 answers

3


The id_cli exists in the two tables you are joining, so SQL does not know which one you want to demonstrate (so it indicates that it is "ambiguous"). It is necessary to indicate which table you want to use.

Try this:

select
  Cli.id_cli, -- nota aqui a indicação de qual tabela vem a coluna 
  nm_cli,
  sum(vl_trn) as tot_trn
from tb_cli Cli
left join tb_trn Tra on (Cli.id_cli = Tra.id_cli)
group by Cli.id_cli, nm_cli; -- necessário também agrupar pelo Cli.id_cli

  • It worked here! Thank you very much! But I was left with a question, I no longer specified which table the columns come from writing "(tb_cli.id_cli = tb_trn.id_cli)"? And after select has From which should also reference the right table. Thanks again.

  • 1

    Yes, you specified in JOIN. However, in the SELECT there can be no ambiguity and as such you really have to indicate which table belongs to the column, if it exists, with the same name, in more than one table.

  • Perfect, I understood. Last doubt, this excerpt "from tb_cli Cli" is creating an alias for tb_cli? Because I switched to "tb_cli as Cli" and it still works. Thank you very much.

  • Yes, that’s exactly it. A keyword AS is optional on most DBMS (including Mysql). Sometimes its use may make the query more readable, but that’s all.

0

What was missing was the group by of the field you want to bring, when no function aggregators do not need, MIN MAX COUNT SUM AVG.

Remember to treat the null value of the transaction if the client has no transaction will return 0.

SELECT c.id_cli,
       c.nm_cli,
       sum(isnull(t.vl_trn, 0))
FROM tb_cli c
LEFT JOIN tb_trn t ON t.id_cli = c.id_cli
GROUP BY c.id_cli
ORDER BY c.id_cli,
         c.nm_cli

Another caveat is if you have client with the same name can return the unexpected value, the right one would be to subquery to bring the name based on the client id and remove from group by: 

SELECT sub.id_cliente,
       cli.nome,
       sub.valor
FROM
  (SELECT c.id_cli AS id_cliente,
          sum(isnull(t.vl_trn, 0)) AS valor
   FROM tb_cli c
   LEFT JOIN tb_trn t ON t.id_cli = c.id_cli
   GROUP BY c.id_cli) AS sub
JOIN tb_cli cli ON cli.id_cli = sub.id_cliente
ORDER BY sub.id_cliente,
         cli.nome

  • True, it’s something to think about, I’m still crawling on SQL. I appreciate the help!

Browser other questions tagged mysql sql query

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