Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: problem with count and sum

    Hello. I have some difficulty when I use the following query. I have a problem with count and sum.
    The joins are all correct.
    select s.id, s.PromoTitle, count(distinct sc.id),
    sum(trx.amount)
    from silver s
    join interest i on s.Id = i.silverId
    join customertransaction trx on i.Id = trx.InterestId
    join silvercertificate sc on i.Id = sc.InterestId
    where i.SystemId='silvers.gr' and i.StatusCode in (301,302,312) and sc.StatusCode in (600,601,612,602)
    and trx.StatusCode IN (401,1001,200,201)
    group by s.id;
    The problem is when I use the silvercertificate table although it shows me the correct count(distinct sc.id) it does NOT show me the correct the other table customertransaction sum(trx.amount).
    And unfortunately I have to use 2 queries which after I join.
    there is some problem with count and sum but I can not find them.
    Also the group by I have tried many other variables but it does not work.

    I have tried and other solution with nested select but still it not brings me the correct results.
    Thank you in advance

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your issue might be same kind of this...
    Quote Originally Posted by tonkuma View Post
    This may be one of typical issues related with JOINs(equal or more than three tables) and GROUP BY.

    Please see the result of before grouping, like ...
    Code:
    SELECT *
     FROM  users                AS u
     LEFT  JOIN
           payment_transactions AS t
      ON   t.trx_user_id   = u.user_id
     LEFT  JOIN
           orders               AS o
      ON   o.order_user_id = u.user_id
    ;

    Try Grouping and SUMs before JOIN.

    Example 1:
    Code:
    SELECT user_name
         , deposits
         , withdrawals
         , orders
     FROM
           users AS u
     LEFT  JOIN
           (SELECT trx_user_id
                 , SUM(CASE WHEN trx_amount > 0 THEN trx_amount ELSE 0 END) AS deposits
                 , SUM(CASE WHEN trx_amount < 0 THEN trx_amount ELSE 0 END) AS withdrawals
             FROM  payment_transactions
             GROUP BY 
    	        trx_user_id
           ) AS t
      ON   t.trx_user_id   = u.user_id
     LEFT  JOIN
           (SELECT order_user_id
                 , SUM(order_amount) AS orders
             FROM  orders
             GROUP BY
                   order_user_id
           ) AS o
      ON   o.order_user_id = u.user_id
    ;
    Please try to SUM in subquery before JOINing, like Example 1.

  3. #3
    Join Date
    Jun 2012
    Posts
    2
    Thank you very much tonkuma. It works.
    I want to know why my solution does not worked?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though, it's not easy to explain by natural language for me,
    I hope the following example might give you some hints to understsand what was happened.

    For an s.Id there might be multiple i.silverId.
    But now, I will assume that there is one i.silverId for an s.Id, to simplify the explanation.
    Code:
    /* After JOIN two tables.
           silver   s
     JOIN  interest i
      ON   i  .silverId   = s.Id
    */
    s.Id | i.silverId | i.Id |
    -----+------------+------+
    1    | 1          | 11   |
    
    /* If there were two rows of trx maching with i.
     JOIN  customertransaction trx
      ON   trx.InterestId = i.Id
    */
    trx.InterestId | trx.amount |
    ---------------+------------+
    11             |         10 |
    11             |         20 |
     
    /* And, if there were three rows of sc maching with i.
     JOIN  silvercertificate   sc
      ON   sc .InterestId = i.Id
    */
    sc.InterestId | sc.id |
    --------------+-------+
    11            |     5 |
    11            |     6 |
    11            |     7 |
    Code:
    /* Afer JOIN all 4 tables.
           silver   s
     JOIN  interest i
      ON   i  .silverId   = s.Id
     JOIN  customertransaction trx
      ON   trx.InterestId = i.Id
     JOIN  silvercertificate   sc
      ON   sc .InterestId = i.Id
    */
    s.Id | i.Id | trx.InterestId | trx.amount | sc.InterestId | sc.id |
    -----+------+----------------+------------+---------------+-------+
    1    | 11   | 11             |         10 | 11            |     5 |
    1    | 11   | 11             |         10 | 11            |     6 |
    1    | 11   | 11             |         10 | 11            |     7 |
    1    | 11   | 11             |         20 | 11            |     5 |
    1    | 11   | 11             |         20 | 11            |     6 |
    1    | 11   | 11             |         20 | 11            |     7 |
    
    /*
     Then GROUP BY s.Id
    */
    s.Id | i.Id | SUM(trx.amount) | COUNT(sc.id) | COUNT(DISTINCT sc.id) |
    -----+------+-----------------+--------------+-----------------------+
    1    | 11   |              90 |            6 |                     3 |

Posting Permissions

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