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

Question

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

Navigation

#1 by (5 votes)

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 I need to return customer payments that 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

It's what I have so far and it's not working

sql sql-server

asked by anonymous 14.12.2018 / 00:55

1 answer

Error in indexes of an array Remove tags generated at the end of the string of a Text Editor

score 5 · Accepted Answer

If I understand what you want, it does not need grouping or summing, it would look 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