Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Posts
    6

    Unanswered: Group by query returning too many rows

    Can anyone help ?
    Why is this query:

    select bp, sum(msg) as 'msg'
    from dbo.net_report
    where gateway = 'sweden'
    and convert(varchar, sqldate, 2) > '02.05.01'
    and convert(varchar, sqldate, 2) <= '02.05.31'
    group by bp

    returning 666 rows, while this query

    select bp
    from dbo.net_report
    where gateway = 'sweden'
    and convert(varchar, sqldate, 2) > '02.05.01'
    and convert(varchar, sqldate, 2) <= '02.05.31'
    group by bp

    is only returning 20.
    The "correct" result is 20 rows, one for each bp.
    The fisrt query returns alot of duplicate bp.

    By the way: What is faster: Converting the sqldate field to a varchar and comparing with another varchar, or converting the varchar to a date and then comparing it to the sqldate field ?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    do you want the SUM of all the msg attributes or the number of msg (messages?) for each bp? try using COUNT(*) in place of the SUM(msg).

    In your case you are probably doing table scans due to the convertion of the date attribute to a varchar. SQL Server can efficiently convert and test a (var)char variable to a date attribute + you can take advantage of indexes.
    Last edited by Paul Young; 06-25-02 at 09:13.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jun 2002
    Posts
    6
    Sorry, I guess I should have made things clearer.
    What I want is the sum of all the numbers stored in the msg column, i.e. the number of msgs for each bp. So the result set should have one row for each bp. This works fine without the sum(msg) part, and the result looks something like this:

    HENNES
    HENTEXTRA
    KANAL5

    But when I add the sum(msg) to get the number of messages pr. bp then the result looks like this:

    Wow. Something strange just happened. When I ran the query to produce the results I added "order by bp" at the end, and then there was suddenly just one row for each 20 in total. Without it the resultset returns 666 rows.

    Is the group by clause dependent upon the order one retrieves the rows ?

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    No. The order by is used to sort the result set and does not affect the group by.

    I set up a simple test...

    Code:
    --------------------------------------------------------------------------------------------
    create table #tmp(f1 varchar(10),f2 int)
    insert into #tmp values('A',2)
    insert into #tmp values('A',4)
    insert into #tmp values('C',3)
    insert into #tmp values('C',1)
    insert into #tmp values('C',1)
    insert into #tmp values('B',2)
    insert into #tmp values('B',3)
    insert into #tmp values('B',4)

    select f1,sum(f2) as 'Sum'
    from #tmp
    group by f1
    order by f1
    --------------------------------------------------------------------------------------------

    is this anything close to what you are working on?
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jun 2002
    Posts
    6
    That is pretty much what I am working on, except that my view has alot more columns. At the moment I am really only interested in getting one row for each bp, with one sum of messages for each.
    My query does produce the desired results, as long as I have the "order by bp" clause at the end.
    So my problem is really solved, but I don't really understand why though. If you want to find out why, and need any more information from me just let me know.
    The view I am querying is based on two other views, but I can't see that making much of a difference.

    This is the result I was looking for, and I get with the order bp:

    davinci 1333
    E-CLIPS 1864
    HENNES 1397
    KANAL5 6470
    MRJET 6
    PASSAGEN 70
    SIMONTV 12
    SPORTAL 828
    STARLIFE 1004
    TISCALI 2484
    YAHOO 3
    ...
    ...
    20 rows in total


    This is some of what I get without the order bp:
    SPORTAL 8
    davinci 11
    E-CLIPS 11
    davinci 1
    E-CLIPS 1
    davinci 7
    E-CLIPS 7
    davinci 9
    E-CLIPS 9
    davinci 2
    E-CLIPS 2
    davinci 8
    ...
    ...
    ...
    666 rows in total

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •