#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0

    Diferent result in sum using group by


    Hi,
    I have a table with actual properties and another table with all properties and also with positive and negative amounts.

    If I do this select using group by property
    I get correctly the amount of money we owe each actual property, and if I sum them all manually with excel I get for exampel 10.000,
    however if I do the same query without the group by I should get the total amount we owe all the properties together, however instead of 10.000 that I got suming I get 18.000.

    I think that is due to the query being wrong and it sums properties we dont have anymore that is in table 2 (properties) but not in table 1 (casa).

    This is the select, and it looks ok to me, I dont understand whats wrong:

    Code:
    select propiedades.id_propiedad, sum(
            case when  MONTH(fecha) = MONTH (CURRENT_DATE) and YEAR(fecha) = YEAR (CURRENT_DATE)
                 then importe
                 else null end
              )  as sum_importe_this_month
         , sum(
            case when  MONTH(fecha) = MONTH (CURRENT_DATE) and YEAR(fecha) = YEAR (CURRENT_DATE)
                 then servicios
                 else null end
              )  as sum_servicios_this_month
    		   , sum(
            case when  MONTH(fecha) = MONTH (CURRENT_DATE) and YEAR(fecha) = YEAR (CURRENT_DATE)
                 then pagado
                 else null end
              )  as sum_pagado_this_month
         , sum(importe)
    	 - sum(servicios)
          -sum(pagado) as total_duenos
      from casa LEFT JOIN propiedades ON casa.id_propiedad = propiedades.id_propiedad where (fecha) <= current_date group by id_propiedad
    Can anybody please help?
    Thanks in advance,
    also I want to add a third table where I have properties we dont have anymore but still owe money, but thats when I get it working with only 2 tables.

    Helen
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Ive tried joins, I even tried this one to make mysql read the left before the right, but I still get the same result:

    FROM casa
    STRAIGHT_JOIN propiedades ON casa.id_propiedad = propiedades.id_propiedad
    WHERE (
    fecha
    ) <= current_date
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    to start with, you should always have GROUP BY when using SUM, unless you want the query to return only a single row (e.g. grand totals)

    if you put propiedades.id_propiedad in the SELECT clause with your sums, then you ~must~ have a GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by r937
    to start with, you should always have GROUP BY when using SUM, unless you want the query to return only a single row (e.g. grand totals)

    if you put propiedades.id_propiedad in the SELECT clause with your sums, then you ~must~ have a GROUP BY clause
    Thanks, didnt notice that, honestly that quest is the one for getting the single totals, just deleted the group by.

    The problem is I want the grand total ie. the same as I get if I sum manually all the totals I get in the group by select.

    In this select I dont select the property (id_propiedad) however the grand total is not equal as all single totals together.

    select sum(
    case when MONTH(fecha) = MONTH (CURRENT_DATE) and YEAR(fecha) = YEAR (CURRENT_DATE)
    then importe
    else null end
    ) as sum_importe_this_month
    , sum(
    case when MONTH(fecha) = MONTH (CURRENT_DATE) and YEAR(fecha) = YEAR (CURRENT_DATE)
    then servicios
    else null end
    ) as sum_servicios_this_month
    , sum(
    case when MONTH(fecha) = MONTH (CURRENT_DATE) and YEAR(fecha) = YEAR (CURRENT_DATE)
    then pagado
    else null end
    ) as sum_pagado_this_month
    , sum(importe)
    - sum(servicios)
    -sum(pagado) as total_duenos
    from casa, propiedades where casa.id_propiedad = propiedades.id_propiedad and (fecha) <= current_date
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by helenp
    ...however the grand total is not equal as all single totals together.
    can you illustrate this with some sample data?

    i don't understand your problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by r937
    can you illustrate this with some sample data?

    i don't understand your problem
    For example with the first select using group by I get several rows like this (from table casa):
    property 1 3246.13
    property 2 3674.85
    property 3 3319.00
    property 4 1898.00
    property 5 2451.31
    property 6 2025.00
    property 7 0.00
    property 8 941.00

    If I manually sum these totals using a calculator, I get as a result for exampel 10.000.
    However if I do the second select to get the total (grand total) of all properties together instead of getting 10.000 wich is the correct grand total I get for exampel 20.000.
    Thanks in advance.
    Hope its clear.
    Helen
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    i'm sorry, what i meant was actual data that actually reflects the problem

    1. your SELECT has 4 or 5 columns, sample data should too

    2. data actually has to add up (not "10,000")

    but let's first find out why you said "it sums properties we dont have anymore"

    also, can you tell me what the primary keys are?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Well, the tables have many mores columns than uses in these selects.
    In table casa we have the name of all actual properties on website
    Table casa
    column
    id_propiedad unique

    Table propiedades
    columns
    id_propiedad, importe, servicios, pagado
    primary id (not used in these selects)

    When a property disappears from website it is not in table casa anymore, but there are several entries in table propiedades, therefore I need table casa to only get the totals for the properties on website at this moment.

    I just said 10.000 as an exampel,
    the total balance (as total_duenos) of all 33 properties using calculator gives me 20.638,35
    However doing this select to get the total balance of all 33 properties together using mysql I get 35228.64
    which is incorrect, therefore I suspected I got in the result properties not in table 1 (casa)
    Thanks, hope its clear
    Helena
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    i am exhausted of ideas

    the problem lies somewhere in your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by r937
    i am exhausted of ideas

    the problem lies somewhere in your data
    Thanks, somewhere in my data, that mean the selects are correct?
    I just dont understand, and exhausted to.
    Thanks anyway.
    Maybe there is some other way to sum the results I get in the group by select?
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,452
    Rep Power
    1751
    Try changing your implict JOIN syntax to explicit and specify an INNER JOIN?

    from:
    Code:
    from casa, propiedades where casa.id_propiedad = propiedades.id_propiedad and (fecha) <= current_date
    to:
    Code:
    from casa
      INNER JOIN propiedades ON casa.id_propiedad = propiedades.id_propiedad
      WHERE fecha <= current_date
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo