using of group by function in sql query

Question

0.00/5 (No votes)

See more:

hi good morning
i had a littel problem with my msaccess 2003 , i am writing the query
by using the Group class is working
and its sample code is

"select b.custrefno,SUM(b.paidamount) as paidamount from paym b,addcust a  where  b.custrefno=a.custrefno GROUP BY b.custrefno";

working prefectly

here it is working in the perfect way , by i need some extra columns so that i had added the Columns names as like name , email,mobile;
so that i had change the query just like this

"select a.email,a.name,a.mobile,b.custrefno,SUM(b.paidamount) as paidamount from paym b,addcust a  where  b.custrefno=a.custrefno GROUP BY b.custrefno";

i had also tryied like this but

"select email,name,mobile,b.custrefno,SUM(b.paidamount) as paidamount from paym b,addcust a  where  b.custrefno=a.custrefno GROUP BY b.custrefno";

but last two are not working executing prefectly , it created the error like

Error: You tried to execute a query that does not include the specified expression 'email' as part of an aggregate function.

where i am going wrong, and what's the wrong is , can any body help me

basycally my requirement is by grouping i need to get the Name and mobile number of that particular record

Posted 19-May-11 20:08pm

sivakumarmr10

Add a Solution

2 solutions

Solution 2

~~Try using "a.email, a.name, a.mobile" to let it know where the datasource is...~~

You don't need that (it's early, I'm still on my first cup of coffee)
Instead, list the email, name and mobile in the GROUP BY clause

[edit]Oops - OriginalGriff[/edit]

Posted 19-May-11 20:42pm

OriginalGriff

Updated 19-May-11 20:45pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

walterhevedeich · Accepted Answer · 2011-05-19T20:41:00

Solution 1

Put the columns that are not inside an aggregate function in the GROUP BY clause. Like this for example.

"select email,name,mobile,b.custrefno,SUM(b.paidamount) as paidamount from paym b,addcust a  where  b.custrefno=a.custrefno GROUP BY b.custrefno,email,name,mobile";

Posted 19-May-11 20:41pm

walterhevedeich