Grouping in SQL query

Discussion in 'ASP General' started by Debbie Davis, Jun 27, 2004.

  1. Debbie Davis

    Debbie Davis Guest

    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!
     
    Debbie Davis, Jun 27, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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], Jun 27, 2004
    #2
    1. Advertising

  3. 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"
     
    Bob Barrows [MVP], Jun 27, 2004
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Wier
    Replies:
    0
    Views:
    451
    David Wier
    Aug 8, 2003
  2. Andrew
    Replies:
    1
    Views:
    1,222
    Hermit Dave
    Jan 26, 2004
  3. Edward
    Replies:
    4
    Views:
    4,626
    William \(Bill\) Vaughn
    Apr 10, 2006
  4. Anonymous
    Replies:
    0
    Views:
    1,516
    Anonymous
    Oct 13, 2005
  5. ecoolone
    Replies:
    0
    Views:
    783
    ecoolone
    Jan 3, 2008
Loading...

Share This Page