Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Grouping in SQL query

Reply
Thread Tools

Grouping in SQL query

 
 
Debbie Davis
Guest
Posts: n/a
 
      06-27-2004
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!
 
Reply With Quote
 
 
 
 
Manohar Kamath [MVP]
Guest
Posts: n/a
 
      06-27-2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!



 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-27-2004
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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
What Happened to Sql Enterprise Manager and Sql Query Analyser in Visual Studio 2005 Edward ASP .Net 4 04-10-2006 04:15 PM
Build dynamic sql query for JSTL <sql:query> Anonymous Java 0 10-13-2005 10:01 PM
slow sql data adapter fil(.) when grouping Andrew ASP .Net 1 01-26-2004 05:16 PM



Advertisments