-
May 26th, 2009, 06:58 PM
#1
Thread Starter
Lively Member
[RESOLVED] SUM Function
hi there.. can someone teach me how to do this.. thanks
Last edited by __wired__; May 26th, 2009 at 07:21 PM.
-
May 26th, 2009, 07:34 PM
#2
Re: SUM Function
SQL Code..
Code:
SELECT SUM(OrderPrice) AS OrderPrice, SUM(OrderPrice1) AS OrderPrice1, Customer
FROM YOURTABLE
GROUP BY Customer
-
May 26th, 2009, 08:10 PM
#3
Thread Starter
Lively Member
Re: SUM Function
thanks for the reply.. can you check my code..
vb Code:
rs.Open ("SELECT SUM(H_RB)as H_RB, SUM(H_HS) as H_HS, SUM(H_SF) as H_SF, SUM(H_DF) as H_DF, NAME from qryMB WHERE TRANDATE>=#" & dtp1.Value & "# and TRANDATE <=#" & dtp2.Value & "#"), conn, 3, 3
it gave me error msg >> "You tried to execute a query that does not include the specified expression 'NAME' as part of an aggregate function".... but when i remove the "NAME"... it will sum all the fields that im trying to sum up.. it shows in 1 row
-
May 26th, 2009, 08:20 PM
#4
Re: SUM Function
You must include Group By clause when using aggregate functions in sql.
Pay attention to details so read jcis sql sample once again.
-
May 26th, 2009, 08:44 PM
#5
Thread Starter
Lively Member
Re: SUM Function
i paste the wrong code.. sorry.. here's the code..
vb Code:
rs.Open "SELECT SUM(H_RB)as H_RB, SUM(H_HS) as H_HS, SUM(H_SF) as H_SF, SUM(H_DF) as H_DF, NAME from qryMB GROUP BY NAME WHERE TRANDATE>=#" & dtp1.Value & "# and TRANDATE <=#" & dtp2.Value & "#", conn, 3, 3
here's the error:
but when i remove the WHERE statement... it's working..i need to filter it out by date..
-
May 26th, 2009, 08:51 PM
#6
Re: SUM Function
You need to learn sql basics - Where clause always comes before Group By or Order By.
Here is a basic structure:
select
from
where
group by
order by
... an so on ...
-
May 26th, 2009, 08:54 PM
#7
Junior Member
Re: SUM Function
use first the WHERE clause then the GROUP by clause
rs.Open "SELECT SUM(H_RB)as H_RB, SUM(H_HS) as H_HS, SUM(H_SF) as H_SF, SUM(H_DF) as H_DF, NAME from qryMB
WHERE TRANDATE>=#" & dtp1.Value & "# and TRANDATE <=#" & dtp2.Value & "#
GROUP BY NAME", conn, 3, 3
Last edited by wysiwyg327; May 26th, 2009 at 08:58 PM.
-
May 26th, 2009, 09:09 PM
#8
Thread Starter
Lively Member
Re: SUM Function
thanks for the help...
here's the code...
vb Code:
rs.Open "SELECT SUM(H_RB)as H_RB, SUM(H_HS) as H_HS, SUM(H_SF) as H_SF, SUM(H_DF) as H_DF, NAME from qryMB WHERE TRANDATE>=#" & dtp1.Value & "# and TRANDATE <=#" & dtp2.Value & "# GROUP BY NAME", conn, 3, 3
-
May 26th, 2009, 10:00 PM
#9
Thread Starter
Lively Member
Re: [RESOLVED] SUM Function
one more thing.. i want to display also the other fields not only the "NAME" what should i type?
Last edited by __wired__; May 26th, 2009 at 10:16 PM.
-
May 26th, 2009, 10:26 PM
#10
Junior Member
Re: [RESOLVED] SUM Function
simple as this:
H_DF, NAME , (new field).....
-
May 26th, 2009, 10:37 PM
#11
Thread Starter
Lively Member
Re: [RESOLVED] SUM Function
for example.. H_DF, NAME, POSITION is the new field.. should i include it in GROUP BY NAME, POSITION <<< ?
-
May 26th, 2009, 10:45 PM
#12
Junior Member
Re: [RESOLVED] SUM Function
sorry my mistake (POSITION = new field), yes you should include it on your GROUP BY clause...hope it works..
-
May 27th, 2009, 12:15 AM
#13
Thread Starter
Lively Member
Re: [RESOLVED] SUM Function
vb Code:
rs.Open "SELECT NAME, LEVEL, SUM(H_RB)as H_RB, SUM(H_HS) as H_HS, SUM(H_SF) as H_SF, SUM(H_DF) as H_DF from qryMB WHERE TRANDATE>=#" & dtp1.Value & "# and TRANDATE <=#" & dtp2.Value & "# GROUP BY NAME, LEVEL", conn, 3, 3
i insert "SELECT NAME, LEVEL, SUM(H_RB)as H_RB... and GROUP BY NAME, LEVEL"...
this is my error..
-
May 27th, 2009, 12:25 AM
#14
Junior Member
Re: [RESOLVED] SUM Function
what particular error do you get?
-
May 27th, 2009, 12:27 AM
#15
Thread Starter
Lively Member
Re: [RESOLVED] SUM Function
when i insert the "LEVEL" in the SELECT and GROUP BY..
-
May 27th, 2009, 12:39 AM
#16
Junior Member
Re: [RESOLVED] SUM Function
Ok lets be specific, i tried to analyze your query and compare it to my previous sql queries with the same function as yours..try to analyze this and make some comparisons..
vb Code:
"SELECT IDno, Project, TaskCode, TaskDescription, DateSave, ISNULL(SUM(RecNo), 0) AS Filesize," & _
"Sum (IsNull(DateDiff(Second, Start1, End1), 0) + IsNull(DateDiff(Second, Start2, End2), 0))/ 3600.00 " & _
"AS Total_Time From Gale WHERE (DateSave BETWEEN '" & Me.dtFromD.Value & "' AND '" & Me.dtToD.Value & "') " & _
"AND (IDno = '" & StrIdno & "') " & _
"GROUP BY IDno, Project, TaskCode, TaskDescription, DateSave " & _
"ORDER BY Project, TaskCode, TaskDescription, DateSave DESC"
-
May 27th, 2009, 12:44 AM
#17
Thread Starter
Lively Member
Re: [RESOLVED] SUM Function
thanks for you help... i think the word "LEVEL" is a reserved word.. coz other fields are working except the field named "LEVEL"... i totally appreciate your help... GOD BLESS
-
May 27th, 2009, 12:51 AM
#18
Thread Starter
Lively Member
Re: [RESOLVED] SUM Function
i changed the field name "LEVEL"... now its totally working... yahhoooo... thanks again
-
May 27th, 2009, 12:56 AM
#19
Junior Member
Re: [RESOLVED] SUM Function
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|