MySQL Sum multiple column values with conditions

Question

I have the following schema (two tables):

**APPS**

   | ID (bigint)  | USERID (Bigint) | USAGE_START_TIME (datetime)    | 
    ------------------------------------------------------------------
   |  1           |        12       |         2013-05-03 04:42:55    |
   |  2           |        12       |         2013-05-12 06:22:45    |
   |  3           |        12       |         2013-06-12 08:44:24    |
   |  4           |        12       |         2013-06-24 04:20:56    |
   |  5           |        13       |         2013-06-26 08:20:26    |
   |  6           |        13       |         2013-09-12 05:48:27    |


**USAGE** 

   | ID (bigint)  | APPID (bigint) |   DEVICEID (bigint)  | HIGH_COUNT (bigint) |  MEDIUM_COUNT (bigint)  |
    --------------------------------------------------------------------------------------------------------
   |  1           |        1       |                  2    |       400           |                   200   |
   |  2           |        1       |                  3    |       200           |                   100   |
   |  3           |        2       |                  3    |       350           |                    40   |
   |  4           |        3       |                  4    |         2           |                   400   |
   |  5           |        4       |                  2    |         4           |                    30   |
   |  6           |        5       |                  3    |        50           |                   300   |

Explanation:

So, there are two tables. Now I want to find the following:

Given a USERID, Get sum of HIGH_COUNT & MEDIUM_COUNT. While counting the SUM it should be taken care that: If in USAGE, same device is used more than once, then the record which has the latest info (based on APPS.USAGE_START_TIME), should be considered while calculating the sum.

For ex:

For above schema, result should be (for userid=12) :

   | HIGH_COUNT (bigint)  | MEDIUM_COUNT (Bigint) |
    -----------------------------------------------
   |                356   |                   470 |

SQL Fiddle: http://sqlfiddle.com/#!2/74ae0f

There is some confusion in the requirement you mean to say that we should only consider the record which has highest value using_start_time — Rohit Choudhary
– Rohit Choudhary, Commented Mar 3, 2014 at 13:36
For example....for which userid is the expected result posted?? — Hackerman
– Hackerman, Commented Mar 3, 2014 at 13:38
I think there is some ambiguity in the question? I am still confused that how you want to latest info and also want to get the sum for a particular user ?? — Rohit Choudhary
– Rohit Choudhary, Commented Mar 3, 2014 at 13:45
Hi all, if there are more than one usage records with same device_id, i want to take into consideration the records with max (latest) usage_start_time. — Pruthvi Raj Nadimpalli
– Pruthvi Raj Nadimpalli, Commented Mar 3, 2014 at 14:36

Andomar · Accepted Answer · 2014-03-03 14:01:56Z

2

If a user uses multiple APPS on one device, this query will use the APPS row with the highest usage_start_time:

select  a.userid
,       sum(u.high_count)
,       sum(u.medium_count)
from    apps a
join    `usage` u
on      u.appid = a.id
join    (
        select  u.device_id
        ,       a.userid
        ,       max(a.usage_start_time) as max_start_time
        from    apps a
        join    `usage` u
        on      u.appid = a.id
        group by
                u.device_id
        ,       a.userid
        ) filter
on      filter.device_id = u.device_id
        and filter.userid = a.userid
        and filter.max_start_time = a.usage_start_time
group by
        a.userid

In your dataset, it will select usage rows 5, 3, 4 for user 12.

See it working at SQL Fiddle.

answered Mar 3, 2014 at 14:01

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andomar Over a year ago

@sash: This answer is per user. Your example answer is with users 12 and 13 combined. Does that explain the difference?

Strawberry · Accepted Answer · 2014-03-03 15:19:49Z

1

I can't quite get your numbers, but something like this should work...

SELECT a.userid
     , SUM(u.high_count)
     , SUM(u.medium_count)
  FROM apps a
  JOIN `usage` u
    ON u.appid = a.id
  JOIN 
     ( SELECT userid
            , deviceid
            , MAX(usage_start_time) max_usage_start_time
         FROM apps a
         JOIN `usage` u
           ON u.appid = a.id
        GROUP
           BY userid
            , deviceid
     ) x
    ON x.userid = a.userid
   AND x.deviceid = u.deviceid
   AND x.max_usage_start_time = a.usage_start_time
 GROUP
    BY userid;

Note that usage is a reserved word. Therefore, this is a bad name for a column (or a table). Also, note inconsistencies between your question and your fiddle.

edited Mar 3, 2014 at 15:19

answered Mar 3, 2014 at 14:14

Strawberry

34k14 gold badges43 silver badges57 bronze badges

1 Comment

Strawberry Over a year ago

That's because you've made a mistake :-)

Dave · Accepted Answer · 2014-03-03 13:33:59Z

-2

I think not had chance to test it but

SELECT SUM(HIGH_COUNT), SUM(MEDIUM_COUNT) FROM `USAGE` INNER JOIN `APPS` ON USAGE.APPID=APPS.ID WHERE APPS.USERID=$input_user_id_to_lookup

will give you your counts.

For yoru other question (homework?) you didn't give us the full schema so we can't guess what you need doing.

Also whoever designed that db should be shot its horrible

answered Mar 3, 2014 at 13:33

Dave

3,2882 gold badges25 silver badges40 bronze badges

1 Comment

Dave Over a year ago

I read it perfectly fine and the query answers his question 100% with the information he had currently posted. We cannot allow for his start time field has he hadn't (at time of me posting) provided the data types of the start time column so we had/have no method of selecting latest entry based off start date.

Collectives™ on Stack Overflow

MySQL Sum multiple column values with conditions

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related