I have a problem on how to display a column subquery that has a different where clause. Here is my code:
SELECT T0.Account AS 'Account', T1.AcctName AS 'AccountName', SUM(ISNULL(T0.Debit,0)) AS 'Debet', SUM(ISNULL(T0.Credit,0)) AS 'Kredit', SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) AS 'Balanse' FROM JDT1 T0 INNER JOIN OACT T1 ON T0.Account=T1.AcctCode WHERE T0.RefDate BETWEEN CONVERT(DATETIME, '01.01.04', 104) AND CONVERT(DATETIME, '31.12.04', 104) GROUP BY T0.Account, T1.AcctName ORDER BY T0.Account
This is my subquery I would include as a first column field. Not sure how to setup with (UNION, IN or ANY etc)
SELECT T0.Account AS 'Konto', T1.AcctName AS 'Kontonavn', SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) AS 'Opening Balanse' FROM JDT1 T0 INNER JOIN OACT T1 ON T0.Account=T1.AcctCode WHERE T0.RefDate < '01.01.04' GROUP BY T0.Account, T1.AcctName ORDER BY T0.Account
This was the subquery which I would like to include as the first sum column. Like this: Account, AccountName, OpeningBalance, SumDebit, SumCredit, EndBalance. OpeningBalance is based on sum of all previous date values in the table. EndBalance is then based on (OpenBalance+(SumDebet-SumKredit)).
Aggregate functions like SUM ignore nulls, so it's not necessary to convert nulls to zeros using ISNULL, or the preferred equivalent COALESCE (preferred because it's the standard SQL function for that purpose).
Based on your use of CONVERT for the datetime values, your database system is SQL Server. In SQL Server, all datetime values carry a time component, so the use of BETWEEN with December 31 on the upper end of the range might actually omit all values for that date. The better approach is to select values that are less than January 1 of the following year.
The subquery that you want to use produces a derived table because it is located in the FROM clause of the query:
select T0.Account , T1.AcctName as AccountName , TD.OpeningBalance , sum(T0.Debit) as Debet , sum(T0.Credit) as Kredit , TD.OpeningBalance +sum(T0.Debit) -sum(T0.Credit) as EndBalance from JDT1 as T0 inner join OACT as T1 on T0.Account = T1.AcctCode left outer join ( select Account , sum(Debit-Credit) as OpeningBalance from JDT1 where RefDate < '2004-01-01' group by Account ) as TD on T0.Account = TD.Account where T0.RefDate >= '2004-01-01' and T0.RefDate < '2005-01-01' group by T0.Account , T1.AcctName , TD.OpeningBalance order by T0.Account
Notice how OpeningBalance is computed in the subquery, and is used both in the SELECT list and the GROUP BY clause as though it were a single column, which, in a derived table, it is.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading