Grouping in SQL query

D

Debbie Davis

Hi there,

SQL 2000

I have the following query:

SELECT sponsor, COUNT(sponsor) * 2 AS total FROM Referrals
GROUP BY sponsor

Works great, returns the sponsor and the total * 2 of their referrals
because that's how much they make per referral. My problem is I need to
pull more information from that table yet still maintain my grouping by
sponsor. Every time I try to add another field, or even * like

SELECT *, sponsor, COUNT(sponsor) * 2 AS total FROM Referrals
GROUP BY sponsor

it wants me to group on all of the fields and I only want to group on
the sponsor. I'm not smart enough to know how to nest these or group
part of it, etc. Hope I'm making sense. Any help is greatly
appreciated. Thanks!


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
M

Manohar Kamath [MVP]

Actually, you can't get around that syntax. What you could do is get
information at detail level, and group the records programmatically. This
will give you more flexibility, but it will mean you have to retrive more
records from DB.
 
B

Bob Barrows [MVP]

Debbie said:
Hi there,

SQL 2000

I have the following query:

SELECT sponsor, COUNT(sponsor) * 2 AS total FROM Referrals
GROUP BY sponsor

Works great, returns the sponsor and the total * 2 of their referrals
because that's how much they make per referral. My problem is I need
to pull more information from that table yet still maintain my
grouping by sponsor. Every time I try to add another field, or even
* like

SELECT *, sponsor, COUNT(sponsor) * 2 AS total FROM Referrals
GROUP BY sponsor

it wants me to group on all of the fields and I only want to group on
the sponsor. I'm not smart enough to know how to nest these or group
part of it, etc. Hope I'm making sense. Any help is greatly
appreciated. Thanks!

Here's my canned explanation for grouping questions such as this:
*************************************************************
Say you have a table with two columns containing the following 4 rows of
data:

Col1 Col2
1 28
1 33
2 5
2 8

Now you decide to create a grouping query:
select Col1 From table Group By Col1
You would get these results:
1
2

Now you decide to add Col2:
select Col1,Col2 From table Group By Col1
Here are the results:
1 ?
2 ?

What do you replace the ?'s with? Do you see the problem?

Bottom line: You have to tell the query engine how to aggregate Col2 (min,
max,sum, avg,count, etc.) so that you wind up with a single row for each
value in Col1.

Even if Col2 contains the value 5 in every row, you still have to tell the
engine how to aggregate it. The query parser does not know what's in your
table: all it can see is that there is a column in the select list that
needs aggregation.

*************************************************************

So, either add the extra columns to your group by list, or use the
appropriate aggregation functions on the extra columns that you put in the
select list. I don't know what your data looks like so I can't get specific.


HTH,
Bob Barrows
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top