How to return table values using the SUM() function?

Asked

Viewed 56 times

2

I have the following tables

CREATE TABLE tbCliente
( ClienteID INT IDENTITY(1,1) PRIMARY KEY,
  ClienteNome VARCHAR(50),
  ClienteTelefone VARCHAR(15),
  ClienteDataCadastro DATE)

 CREATE TABLE tbPagamento
( PagamentoID INT IDENTITY(1,1) PRIMARY KEY,
  ClienteID INT,
  PedidoID INT,
  PagamentoValor DECIMAL(8,2),
  PagamentoData DATE)

CREATE TABLE tbPedido
( PedidoID INT IDENTITY(1,1) PRIMARY KEY,
  PedidoData DATE,
  ClienteID INT)

CREATE TABLE tbProduto
( ProdutoID INT IDENTITY(1,1) PRIMARY KEY,
  ProdutoNome VARCHAR(50),
  ProdutoValor DECIMAL(5,2))

CREATE TABLE tbPedidoItem
( PedidoItemID INT IDENTITY(1,1) PRIMARY KEY,
  PedidoID INT,
  ProdutoID INT)

ALTER TABLE tbPagamento  WITH CHECK 
ADD  CONSTRAINT FK_tbPagamento_tbCliente
FOREIGN KEY(ClienteID) REFERENCES tbCliente (ClienteID)

ALTER TABLE tbPagamento  WITH CHECK 
ADD  CONSTRAINT FK_tbPagamento_tbPedido
FOREIGN KEY(PedidoID) REFERENCES tbPedido (PedidoID)

ALTER TABLE tbPedido  WITH CHECK 
ADD  CONSTRAINT FK_tbPedido_tbCliente
FOREIGN KEY(ClienteID) REFERENCES tbCliente (ClienteID)

ALTER TABLE tbPedidoItem  WITH CHECK 
ADD  CONSTRAINT FK_tbPedidoItem_tbPedido
FOREIGN KEY(PedidoID) REFERENCES tbPedido (PedidoID)

ALTER TABLE tbPedidoItem  WITH CHECK 
ADD  CONSTRAINT FK_tbPedidoItem_tbProduto
FOREIGN KEY(ProdutoID) REFERENCES tbProduto (ProdutoID)

and need to return payments from customers who were greater than 50

select soma.ClienteID
from ( select tbCliente.ClienteID, sum(tbPagamento.PagamentoValor > 50) as preco
        from tbCliente
        group by ClienteID
        having sum(tbPagamento.PagamentoValor > 50,0) as soma
        inner join tbPagamento
        on tbPagamento.ClienteID = soma.ClienteID

is what I’ve got so far and it’s not working

sql sql-server

  • Do you need to return a list of payments greater than 50? Or a list of customers whose sum of payments is > 50?

  • List of payments greater than 50, with each customer’s name

  • 1

    So why do you think you need sum and grouping?

1 answer

5


If I understood what you want, it doesn’t need grouping or summation, it would be something like this:

SELECT 
    tbCliente.ClienteID, 
    tbCliente. ClienteNome, 
    tbPagamento.PagamentoValor
FROM tbCliente
INNER JOIN tbPagamento 
ON tbPagamento.ClienteID = tbCliente.ClienteID
WHERE tbPagamento.PagamentoValor > 50
ORDER BY tbPagamento.PagamentoData

Browser other questions tagged sql sql-server

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