0

I'm doing a exercise about SQL query and I came up with an error when it comes to SUM. I know what is does, but I somehow get an error...

select 
    client.LName, client.FName, 
    COUNT(b.Total) 
from 
    ClientBank client 
INNER JOIN 
    Bank b ON (client.ClientID = b.ClientID) 
where
    client.LName = 'name' AND client.FName = 'a';

But then I get this error...

Column 'ClientBank.LName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I have tried to do a Group BY, don't really know how to use it well.. Any help would be great!

user3342795
  • 105
  • 3
  • 6
  • Do you want number of rows? the sum of *total* for a client? What are you expecting from COUNT(b.Total)? – kums Oct 02 '14 at 04:48
  • My apolagies, I want to sum of total , which is SUM and not count, but I do still get the same error – user3342795 Oct 02 '14 at 16:50

1 Answers1

1

You have not given the structure of the ClientBank and Bank tables. I am taking that you inserted records something like this:

INSERT INTO `ClientBank`
 (`ClientID`, `FName`, `Lname`)
VALUES 
    (1, 'Abraham', 'Backman'),
    (2, 'Carl',    'Dacosta'),
    (3, 'Erwin',   'Fabio'),
    (4, 'Gabriel', 'Haddon');

INSERT INTO `Bank`
 (`DepositID`, `ClientID`, `Deposit`)
VALUES
    (1, 1, 100),
    (2, 2, 200),
    (3, 3, 300),
    (4, 4, 400),
    (5, 2, 500),
    (6, 3, 600);

You can get the total of deposit for each client with this:

SELECT
    client.LName, client.FName, SUM(b.Deposit) as Total
FROM
    ClientBank client 
INNER JOIN 
    Bank b ON (client.ClientID = b.ClientID) 
GROUP By
    client.LName, client.FName;

If you want only for a particular client (filtering using their name than Client Id):

SELECT
    client.LName, client.FName, SUM(b.Deposit) as Total
FROM
    ClientBank client 
INNER JOIN 
    Bank b ON (client.ClientID = b.ClientID) 
WHERE
    client.LName = 'Fabio' AND client.FName = 'Erwin'
GROUP By
    client.LName, client.FName;
kums
  • 2,661
  • 2
  • 13
  • 16
  • Adding both last name and first name in the group by fixed the problem, I feel soo stupid now! Thanks you for the great help :D – user3342795 Oct 02 '14 at 21:34