Maximum value per category

Asked

Viewed 49 times

0

I am doing a job in which I would like to show the maximum balance of each account type with subselect within the SELECT.

Something more or less like this:

CodConta ---------------------tipo ------------------ saldo maximo
2568468                      Salario                  6200
7895465                      Corrente                 9000
2315467                      Poupança                 32000

The table is called "account" and has the data:

  • codconta
  • guy
  • balance
  • Opening date
  • Agency

I got something similar, but shows only the values and one for each column:

SELECT
  MAX((SELECT saldo FROM conta WHERE tipo = 'Salario' AND codconta = s1.codconta)) "Conta salario",
  MAX((SELECT saldo FROM conta WHERE tipo = 'Corrente' AND codconta = s1.codconta)) "Conta corrente",
  MAX((SELECT saldo FROM conta WHERE tipo = 'Poupança' AND codconta = s1.codconta)) "Conta Poupança"
FROM(
  SELECT
    *
  FROM conta
)s1

Table structure:

CREATE TABLE Conta (
      CodConta NUMBER(6) NOT NULL PRIMARY KEY,
      Tipo VARCHAR2(30) NOT NULL,
      Saldo DECIMAL(12,2) NOT NULL,
      Data_Abertura DATE,
      Agencia NUMBER(5) NOT NULL,
      FOREIGN KEY(Agencia) REFERENCES Agencia(NumAgencia)
   )
sql select

  • need to be by sub-query itself? pq this is easy and much simpler using group by

  • Select type,max(balance) as saldomax from tabela group by tipo vide https://www.w3schools.com/sql/sql_groupby.asp

  • I will have to think of another situation that forces me to use a subquery in select, as it is a job exercise asks for this

1 answer

0


Hello, using GROUP BY must resolve, ex:

SELECT tipo, MAX(saldo) AS 'saldo' FROM conta GROUP BY TIPO;

Let me know if it works. Thanks!

  • This code worked, thank you very much, the problem is that I needed to elaborate a situation in which it is necessary to use a Subselect within select and my difficulty is to elaborate it. Statements like: "Show maximum balance for each account type" and then mount an SQL

Browser other questions tagged sql select

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