inner join with sum of one column of second table

1
votes
SELECT accounts.lconm, accounts.netname, accounts.adress, totsls.total 
FROM accounts 
INNER JOIN (SELECT activestb, SUM(billamnt) AS total 
            FROM bills 
            GROUP BY bils.acc) totals ON totals.acc = accounts.acc 
WHERE acc = 1

I want to sum of billamnt which is in the second table(bills) with this query but I m getting an error in SQL Server compact edition 2008.

The error message is :

Major Error 0x80040E14, Minor Error 25515 select accounts.lconm,accounts.netname, accounts.adress, totsls.total from accounts left outer join (select activestb, sum(billamnt) as total from bills group by bils.acc)totals on totals.acc=accounts.acc where acc=1
In aggregate and grouping expressions, the SELECT clause can contain only aggregates and grouping expressions. [ Select clause = ,activestb ]

4
sqlsql-serversql-server-2008
SQL Server Compact Edition has version numbers like 3.5, 4.0 - not years (like 2008). So it this really for SQL Server CE - or is it for the full version of SQL Server 2008 ??? ? – marc_s
Possible duplicate of i want to get all column of Accounts table with this query but it is giving error – trincot

4 Answers

0
votes

It should be like below:

select accounts.lconm,accounts.netname, accounts.adress, totsls.total,accounts.acc from accounts 
left outer join 
(select activestb,bills.acc,sum(billamnt) as total from bills group by bills.acc,bills.activestb)totals
on totals.acc=accounts.acc where totals.acc=1
0
votes

Try this query:

SELECT a.lconm,
       a.netname,
       a.adress,
       t.total 
FROM accounts a
INNER JOIN (
    SELECT acc, SUM(billamnt) AS total 
    FROM bills 
    GROUP BY acc
) t ON t.acc = a.acc 
WHERE a.acc = 1

As you can see, I just refactored your query. Few remarks, that you should keep in mind:

  1. When you use GROUP BY, you shouldn't put in SELECT list columns that aren't aggregated or aren't listed in GROUP BY, I changed that in your subquery.

  2. When you use JOIN, it's good practice to use table aliases throughout the query, you didn't in your WHERE clause. It's alright, when column name is unique, which in your case isn't (acc column is present in both queries).

Also, I prefer short aliases (it could reduce readability), but ift's just preference.

Also notice that I formatted your query differently. Correct formatting is crucial for your code to be more readable :)

0
votes

The problem in your query is the GROUP BY clause in the subquery. It needs to refer to columns in the subquery. In addition, acc is present in both the accounts table and the subquery, so you need to qualify that column.

So, this nominally fixes your problem:

SELECT a.lconm, a.netname, a.adress, b.total 
FROM accounts a INNER JOIN
     (SELECT b.acc, SUM(b.billamnt) AS total 
      FROM bills b
      GROUP BY bils.acc
     ) b
     ON b.acc = a.acc 
WHERE a.acc = 1;

However, this is an inefficient way to write the query, because all the bills have to be aggregated, and you are only selecting one of them. One solution is to repeat the where clause in the subquery. That can be cumbersome and prone to error.

Another solution is a correlated subquery:

SELECT a.lconm, a.netname, a.adress,
       (SELECT SUM(b.billamnt) AS total 
        FROM bills b
        WHERE b.acc = a.acc 
       ) as total
FROM accounts a INNER JOIN
WHERE a.acc = 1;

This sums the amounts from bills only for the given account. With an index on bills(acc, billamnt) it should be very fast.

0
votes

According to the official documentation，‘The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list. However, each table or view column in any nonaggregate expression in the list must be included in the GROUP BY list’， it will indicate the cause of your error.

For more details , you can refer to this link and you will see some examples about your issue: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017#arguments

There is a great example to explain for you : https://www.codeproject.com/Articles/1110163/%2FArticles%2F1110163%2FSQL-GROUP-By-and-the-Column-name-is-invalid-in-the

Please try following script.

SELECT accounts.lconm, accounts.netname, accounts.adress, totsls.total 
FROM accounts 
INNER JOIN (SELECT activestb,acc, SUM(billamnt) AS total 
            FROM bills 
            GROUP BY acc,activestb) totals ON totals.acc = accounts.acc 
WHERE acc = 1

Best Regards, Rachel