Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 9 of 9 total |
query that will sum union of sums |
Fri, Oct 3 2008 6:12 PM | Permanent Link |
Khaled Homouda | Hello all, and thanks for reading, I need to have a query that will sum union of sums, ie
SELECT SUM(Accounting.Paid) AS SumPaid, EXTRACT(MONTH FROM Accounting.Date) AS MonthNo FROM Accounting WHERE Accounting.Date BETWEEN '2008-1-1' AND '2008-10-1' AND EXTRACT(YEAR FROM Accounting.Date) = 2008 Group By MonthNo UNION ALL SELECT SUM(OperateFees.Paid) AS SumPaid, EXTRACT(MONTH FROM OperateFees.Date) AS MonthNo FROM OperateFees WHERE OperateFees.Date BETWEEN '2008-1-1' AND '2008-10-1' AND EXTRACT(YEAR FROM OperateFees.Date) = 2008 Group By MonthNo Now I need to sum SumPaid of the above result set SELECT SUM(SumPaid), MonthNo From ... How can I do that thanks Khaled |
Fri, Oct 3 2008 6:25 PM | Permanent Link |
Khaled Homouda | What I need is to sum by MonthNo ie 1 , 2, 3, 4 etc
thanks Khaled |
Fri, Oct 3 2008 6:27 PM | Permanent Link |
"Robert" | "Khaled Homouda" <Khomouda@qualitynet.net> wrote in message news:32918172-25B1-4066-BBA5-89BCE08D09AE@news.elevatesoft.com... > Hello all, and thanks for reading, I need to have a query that will sum > union of sums, ie > > SELECT SUM(Accounting.Paid) AS SumPaid, EXTRACT(MONTH FROM > Accounting.Date) AS MonthNo into memory\temp (only needed in the first select) > FROM Accounting > WHERE Accounting.Date BETWEEN '2008-1-1' AND '2008-10-1' AND EXTRACT(YEAR > FROM > Accounting.Date) = 2008 > Group By MonthNo > UNION ALL > SELECT SUM(OperateFees.Paid) AS SumPaid, EXTRACT(MONTH FROM > OperateFees.Date) AS MonthNo > FROM OperateFees > WHERE OperateFees.Date BETWEEN '2008-1-1' AND '2008-10-1' AND EXTRACT(YEAR > FROM > OperateFees.Date) = 2008 > Group By MonthNo; <<<<<< script to be continued > > Now I need to sum SumPaid of the above result set > SELECT SUM(SumPaid), MonthNo From memory\temp > Robert |
Fri, Oct 3 2008 6:30 PM | Permanent Link |
Fernando Dias Team Elevate | Khaled,
You can do that using a temporary table in a script: DROP TABLE IF EXISTS "TempTbl" ; SELECT SUM(Accounting.Paid) AS SumPaid, EXTRACT(MONTH FROM Accounting.Date) AS MonthNo INTO "TempTbl" FROM Accounting WHERE Accounting.Date BETWEEN '2008-1-1' AND '2008-10-1' AND EXTRACT(YEAR FROM Accounting.Date) = 2008 Group By MonthNo UNION ALL SELECT SUM(OperateFees.Paid) AS SumPaid, EXTRACT(MONTH FROM OperateFees.Date) AS MonthNo FROM OperateFees WHERE OperateFees.Date BETWEEN '2008-1-1' AND '2008-10-1' AND EXTRACT(YEAR FROM OperateFees.Date) = 2008 Group By MonthNo ; SELECT SUM(SumPaid), MonthNo From "TempTbl" GROUP BY MonthNo ; -- Fernando Dias [Team Elevate] |
Fri, Oct 3 2008 6:40 PM | Permanent Link |
Khaled Homouda | Thanks for your replies, then should it be a different query? because the SQL I write is
in Query.SQL.ADD code updating monthNo or YearNo or DayNo and dates fro comboboxes Khaled |
Sat, Oct 4 2008 11:45 AM | Permanent Link |
"Robert" | "Khaled Homouda" <Khomouda@qualitynet.net> wrote in message news:B36FBBEA-A425-423F-BC6D-E9C2BC48A2EC@news.elevatesoft.com... > Thanks for your replies, then should it be a different query? because the > SQL I write is > in Query.SQL.ADD code updating monthNo or YearNo or DayNo and dates fro > comboboxes No problem. Just make sure you end each step with ; select ... from t; select .... from t2; etc. Robert > > Khaled > |
Sat, Oct 4 2008 11:46 AM | Permanent Link |
"Robert" | "Khaled Homouda" <Khomouda@qualitynet.net> wrote in message news:EB53B6A1-87F2-481F-B132-8FED42E35965@news.elevatesoft.com... > What I need is to sum by MonthNo ie 1 , 2, 3, 4 etc > thanks > The usual select sum(money) mymoney, month from memory\temp group by month Robert > Khaled > |
Sat, Oct 4 2008 11:52 AM | Permanent Link |
"Robert" | is it better to use UNION ALL or to force the items to be different by
adding some character? Option 1 select a, b from t1 union all select a, b from t2; Option 2 select 1, a, b from t1 union select 2, a, b from t2 seems to me option 1 is better, because you don't need to sort the result. True? Robert |
Mon, Oct 6 2008 12:04 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Robert,
<< seems to me option 1 is better, because you don't need to sort the result. True? >> Yes. UNION ALL is just a record dump, whereas UNION requires a temporary index and some comparisons. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |