inner join with sum of one column of second table

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 :)

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.

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