Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Sum in query not producing correct results?

    My desire is to show . . .

    =Sum[COLDWELL BANKER NRT*] AS "COLDWELL BANKER NRT"
    =Sum NOT[COLDWELL BANKER NRT*] AS "ALL OTHERS"

    Code:
    SELECT Sum(qryBeachCities.SalePrice) AS [COLDWELL BANKER NRT], Sum(qryBeachCities.SalePrice) AS [ALL OTHERS]
    FROM qryBeachCities
    GROUP BY qryBeachCities.City, qryBeachCities.City, qryBeachCities.ListName
    HAVING (((qryBeachCities.City)="NB" Or (qryBeachCities.City)="NC" Or (qryBeachCities.City)="CDM" Or (qryBeachCities.City)="LB" Or (qryBeachCities.City)="DP") AND ((qryBeachCities.ListName) Like "COLDWELL BANKER NRT*")) OR (((qryBeachCities.City)="NB" Or (qryBeachCities.City)="NC" Or (qryBeachCities.City)="CDM" Or (qryBeachCities.City)="LB" Or (qryBeachCities.City)="DP") AND ((qryBeachCities.ListName) Not Like "COLDWELL BANKER NRT*"));
    I seem to be missing the obvious!

    Thanks . . . Rick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are missing the obvious parentheses

    =Sum([COLDWELL BANKER NRT*])
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Almost there with query

    Okay, this query is working, however, I'm looking for a different sum.

    The query is summing each occourance of [COLDWELL BANKER NRT*] and each occourance of [ALL OTHERS]

    I'm looking for results as . . .

    [COLDWELL BANKER NRT*] $96,599,300 [ALL OTHERS] $273,950,40

    Thanks . . . Rick

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    I got it!

    I removed the GROUP BY clause.

    Thanks . . . Rick

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    I spoke to soon!

    This is producing the exact same result for both COLDWELL BANKER NRT and ALL OTHERS.

    Code:
    SELECT Sum(Scmls.SalePrice) AS [COLDWELL BANKER NRT], Sum(Scmls.SalePrice) AS [ALL OTHERS]
    FROM Scmls
    HAVING (((Scmls.ListName) Like ("COLDWELL BANKER NRT*")) AND ((Scmls.City)="NB" Or (Scmls.City)="NC" Or (Scmls.City)="CDM" Or (Scmls.City)="LB" Or (Scmls.City)="DP" Or (Scmls.City)="SC")) OR (((Scmls.ListName) Not Like ("COLDWELL BANKER NRT*")) AND ((Scmls.City)="NB" Or (Scmls.City)="NC" Or (Scmls.City)="CDM" Or (Scmls.City)="LB" Or (Scmls.City)="DP" Or (Scmls.City)="SC"));
    I've fooled around but I can't seem to get it right. As soon as I add the ALL OTHERS fields necessary for the query the query go south giving the same total for both.

    Any ideas . . . Rick

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Each has the same Sum, so how could they be different?

    SELECT Sum(IIf(ListName like "COLDWELL BANKER NRT*",[SalePrice],0)) AS TotalCOLDWELL, Sum(IIf(ListName like "COLDWELL BANKER NRT*",0,[SalePrice])) AS TotalNot ...
    Paul

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Access simple Sum not simple!

    Code:
    SELECT Sum(Scmls.SalePrice) AS SumOfSalePrice
    FROM Scmls
    HAVING (((Scmls.City)="NB" Or (Scmls.City)="CDM" Or (Scmls.City)="NC" Or (Scmls.City)="LB" Or (Scmls.City)="DP" Or (Scmls.City)="SC") AND ((Scmls.ListName) Like "COLDWELL BANKER NRT*"));
    The above works perfectly.

    I need (in the same query) to run the sum for NOT LIKE "COLDWELL BANKER NRT*" AS "ALL OTHES"

    After 8 hours I've tried everything I know. I'm whipped!

    Something like
    Code:
    Field                    Field
    SalePrice                ListName
    =Sum()                   Like "COLDWELL BANKER NRT*"
    =Sum()                   NOT Like "COLDWELL BANKER NRT*"
    I should be something like . . .
    Code:
    ListName                           ListName
    "COLDWELL BANKER NRT*"            "ALL OTHERS"
    $71,213,900                       $190,694,901
    Any ideas?

    Thanks . . . Rick

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Did you miss post 6? It should produce 2 fields, the sum of Coldwell and the sum of all others.
    Paul

  9. #9
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Whalla!!!

    This one works!

    Code:
    SELECT 
    Sum(IIf(S.ListName Like ("COLDWELL BANKER NRT*") And (S.ListName) Like ("COLDWELL BANKER NRT*"),S.SalePrice,0)) AS [COLDWELL BANKER NRT],
    Sum(IIf(S.ListName Not Like("COLDWELL BANKER NRT*")  And (S.ListName) Not Like ("COLDWELL BANKER NRT*"),S.SalePrice,0)) AS [ALL OTHERS]
    FROM SCMLS AS S
    HAVING (((S.City)="NB" Or (S.City)="CDM" Or (S.City)="NC" Or (S.City)="LB" Or (S.City)="DP" Or (S.City)="SC"));
    It's a little ugly. Can someone please help me clean it up some?

    Thanks much . . . Rick

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber
    It's a little ugly. Can someone please help me clean it up some?
    Code:
    select sum(iif(ListName like 'COLDWELL BANKER NRT*' 
               ,SalePrice,0)) as [COLDWELL BANKER NRT]
         , sum(iif(ListName like 'COLDWELL BANKER NRT*'
               ,0,SalePrice)) as [ALL OTHERS]
      from SCMLS 
     where City in ('NB','CDM','NC','LB','DP','SC')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Thanks Rudy . . .

    At first when I looked at this I couldn't understand why it worked as both references, (ListName like 'COLDWELL BANKER NRT*'

    I was hung up on the Like and Not Like scenario.

    But then I saw the . . . ,SalePrice,0)) and the ,0,SalePrice)).

    I've learned something valuable here.

    Thanks much Rudy.

    Who's going to win Sundays game?

    Rick

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the kind words

    slow and steady, with careful inspection, will reveal much

    colts and bears
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    slow and steady, with careful inspection, will reveal much
    Wax on wax off
    Quote Originally Posted by r937
    colts and bears
    The mortgage is duly on it
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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