|
Home > Archive > IIS ASP > June 2004 > Grouping in SQL query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Grouping in SQL query
|
|
| Debbie Davis 2004-06-28, 8:55 am |
| 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!
| |
| Manohar Kamath [MVP] 2004-06-28, 8:55 am |
| 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.
--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"Debbie Davis" <dd@clickfocal.com> wrote in message
news:eaN8jiHXEHA.4032@TK2MSFTNGP11.phx.gbl...
> 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!
| |
| Bob Barrows [MVP] 2004-06-28, 8:55 am |
| Debbie Davis wrote:
> 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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
|
|
|
|
|