Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Dissolve based on common value

    I have an Access database like so:

    [field1] [field2] [field3]
    joe shmo 20
    cathy shmo 30
    john big 80
    jack smith 50

    Now I want to run a SQL query where the result is something like this:

    the [shmo]s have [20]+[30]=50 apples
    the [big]s have [80]=80 apples
    the [smith]s have [50]=50 apples

    Basically, based on common values in [field2], perform operation on values in [field3].

    Thanks
    Accessor

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    get yourself into A's query design gid
    show your table
    drag [field2] and [field3] into the grid (you don't want [field1])
    hit the SIGMA button in the tool bar.
    (your two fields now have "Group By" showing in the grid)
    change "Group By" under [field3] to "Sum"

    ....and check out the query in SQL view.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Posts
    4

    Dissolve based on common value

    It says "Data type mismatch" because it was trying to sum/group-by a text [FIELD2] with a number field [FIELD3].

    BTW, can you give me the SQL statement?

    This is what I have been thinking:
    Create an array of all values in [FIELD2] using ASP/PHP/etc. Then compare each value in [FIELD2] with every other value in [FIELD2]. Then, for all matches, sum corresponding values in [FIELD3].

    Again, ex:
    [FIELD1] [FIELD2] [FIELD3]
    North China 20
    Central Japan 30
    South China 10

    The output desired would be like:
    'China' has '30' apples
    'Japan' has '30' apples

    Regards
    Accessor

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it works in Access - promise! (you can GROUP BY text, date, integer, id, boolean, anything else you can think of)

    ASP/PHP/BLT/SOS... not my thing.

    SQL is along the lines of:

    SELECT table.field1, Sum(table.field2) as WhatYouAreLookingFor
    FROM table
    GROUP BY table.field1

    perhaps you are getting stuck trying to sum a text field.

    if you are confident that all your numbers-as-text are valid numbers, try

    ...., cint(Sum(table.field2)) as....

    or some other cxxx function to suit whatever your data is.

    izy

    this is a load of crap!
    maybe ...., Sum(cint(table.field2)) as ... might be less stupid!
    anyhow - you seem to have the answer now. izy 11 Feb
    Last edited by izyrider; 02-11-04 at 12:35.
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and just to make sure your GROUP BY is working as planned,
    replace sum()
    by count()

    count doesn't care what the counted datatype is, so it should isolate the problem to sum() or the GROUP BY

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Feb 2004
    Posts
    4
    I tried a couple of permutations. Yes, the SUM() does add up the numbers the right way, so know we know it works. But what I really want is to perform % calculations on values from 3 different fields based on common values in 1 field. ex:

    [field1] [field2] [field3] [field4]
    Club 10 30 33.3%
    Class 20 80 25.0%
    Club 18 100 18.0%
    Class 15 15 100.0%

    So, the desired result would be:
    The Club has [100*(10+18)/(30+100)]=21.5% geeks
    and
    the Class has [100*(20+15)/(80+15)]=36.8% geeks.

    Thanks izy!

  7. #7
    Join Date
    Feb 2004
    Posts
    199
    SELECT table.field1, 100*Sum(table.field2)/Sum(table.field3) as res
    FROM table
    GROUP BY table.field1

Posting Permissions

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