mysql - group by multiple columns and sum

Question

I'm having some trouble figuring out the following mysql query.

This is meant to be a sort of time tracking where users have certain rates depending on the task they did. And I require to have a summary so I know what to pay the users at the end of the week.

-----------tSessions---------------------------
|id      |userid  |typeid  |session_length_min
-----------------------------------------------
|1       |1       |1       |30
-----------------------------------------------
|2       |1       |1       |45
-----------------------------------------------
|3       |1       |2       |(null)
-----------------------------------------------
|4       |2       |2       |(null)
-----------------------------------------------


-----------tUsers-----------------------------------------------------------------------------------
|id      |name  |rate_cleaning_30_min   |rate_cleaning_45_min   |rate_kitchenwork  |rate_dogwalking
----------------------------------------------------------------------------------------------------
|1       |Tom   |30                     |50                     |40                |20
----------------------------------------------------------------------------------------------------
|2       |Joe   |35                     |60                     |45                |20
----------------------------------------------------------------------------------------------------
|3       |Dave  |40                     |60                     |30                |10
----------------------------------------------------------------------------------------------------


-----------tTypes----------------
|id      |name
---------------------------------
|1       |Cleaning
---------------------------------
|2       |Kitchenwork
---------------------------------
|3       |Dogwalking
---------------------------------


============== Required Result ====================
|username   |sessioncount   |amount_to_pay
---------------------------------------------------
|Tom        |2              |120
---------------------------------------------------
|Joe        |1              |45
---------------------------------------------------

The query below is one if my not so successful approaches:

SELECT
tSessions.id,
tSessions.userid,
tSessions.typeid,
tSessions.session_length_min,
SUM(tUsers.rate_cleaning_30_min) AS rate_cleaning_30_min_sum,
SUM(tUsers.rate_cleaning_45_min) AS rate_cleaning_45_min_sum,
SUM(tUsers.rate_kitchenwork) AS rate_kitchenwork,
SUM(tUsers.rate_dogwalking) AS rate_dogwalking,
Count(*) AS sessioncount,
FROM
tSessions
INNER JOIN tUsers ON tSessions.userid = tUsers.id
WHERE WEEKOFYEAR(FROM_UNIXTIME(datetime))=WEEKOFYEAR(NOW())-1
GROUP BY
tSessions.userid,
tSessions.typeid

Answer 1

There is some problem in your table design ie... if you add one more row in your 'tTypes' table then you have to add one column in your 'tUsers' table. That is not a good design.

I slightly modified your 'tUsers' table and added one more table named 'trates'

New tUses table structure is given below

new 'trates' table will be like

Script to create new 'trates' table is given below

CREATE TABLE `trates` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `typeid` INT(11) NOT NULL DEFAULT '0',
    `userid` INT(11) NOT NULL DEFAULT '0',
    `rate` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=13;

Script to insert you data to 'trates' table is given below

INSERT INTO `trates` (`id`, `typeid`, `userid`, `rate`) VALUES
    (1, 1, 1, 30),
    (2, 2, 1, 50),
    (3, 3, 1, 40),
    (4, 4, 1, 20),
    (5, 1, 2, 35),
    (6, 2, 2, 60),
    (7, 3, 2, 45),
    (8, 4, 2, 20),
    (9, 1, 3, 40),
    (10, 2, 3, 60),
    (11, 3, 3, 30),
    (12, 3, 3, 10);

Advantage of this design is if you add one more type in 'tTypes' table then you just need to insert one row in 'trates' table instead of creating one column in you old table.

I used same 'tSessions' and 'tTypes' tables..

After creating my new tables you can use following query to get required result

select usr.name, (select  count(distinct userid,typeid) 
 from tsessions tsess where
 tsess.userid=usr.id ) as 'sessioncount', sum(rate) as 'amount_to_pay'
 from tusers usr,trates rts,tsessions sess1 where rts.userid=usr.id
  and rts.typeid=sess1.typeid and sess1.userid=usr.id  
group by usr.name 

sessioncount is 3 for Tom because i add separate rows for 'cleaning_30_min' and 'cleaning_45_min' in 'tTypes' table. I think 3 is the correct sessioncount in this case

Answer 2

I believe the problem here is that your data is not written correctly.
First off, you need a rates table :

CREATE TABLE `trates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `type_id` int(11) NOT NULL,
  `rate` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`user_id`,`type_id`)
) 

Populate the table with the correct rates per person:

INSERT INTO trates VALUES (NULL,(SELECT id FROM tusers WHERE NAME = 'Tom'),(SELECT id FROM ttypes WHERE NAME = 'Cleaning'),30);
 INSERT INTO trates VALUES (NULL,(SELECT id FROM tusers WHERE NAME = 'Tom'),(SELECT id FROM ttypes WHERE NAME = 'Kitchenwork'),40);
 INSERT INTO trates VALUES (NULL,(SELECT id FROM tusers WHERE NAME = 'Tom'),(SELECT id FROM ttypes WHERE NAME = 'Dogwalking'),20);

 INSERT INTO trates VALUES (NULL,(SELECT id FROM tusers WHERE NAME = 'Joe'),(SELECT id FROM ttypes WHERE NAME = 'Cleaning'),35);
 INSERT INTO trates VALUES (NULL,(SELECT id FROM tusers WHERE NAME = 'Joe'),(SELECT id FROM ttypes WHERE NAME = 'Kitchenwork'),45);
 INSERT INTO trates VALUES (NULL,(SELECT id FROM tusers WHERE NAME = 'Joe'),(SELECT id FROM ttypes WHERE NAME = 'Dogwalking'),20);

 INSERT INTO trates VALUES (NULL,(SELECT id FROM tusers WHERE NAME = 'Dave'),(SELECT id FROM ttypes WHERE NAME = 'Cleaning'),40);
 INSERT INTO trates VALUES (NULL,(SELECT id FROM tusers WHERE NAME = 'Dave'),(SELECT id FROM ttypes WHERE NAME = 'Kitchenwork'),30);
 INSERT INTO trates VALUES (NULL,(SELECT id FROM tusers WHERE NAME = 'Dave'),(SELECT id FROM ttypes WHERE NAME = 'Dogwalking'),10);

Next, there is a problem with the rate types here, since you're referring to 2 combined pieces of data - Work Type + Time of work
Cleaning 30 minutes
Cleaning 45 minutes
Does that mean that cleaning can only be done in these two?
Can other tasks be 30 and 45 minutes?
Rates are usually per hour, meaning if a rate is 20$ per hour and a person worked 120 minutes, it should be TimeSpent/60*PayPerHourRate (120/60*20)

If it's always 30 and 45 minutes, I would suggest adding a value to your types table:

id  name
1   Cleaning30m
2   Kitchenwork
3   Dogwalking
4   Cleaning45m

From this point summing the data is easy :)

Answer 3

Here is a SQLFiddle example:

select tUsers.Name,
count(*) as sessioncount,
sum(
if(typeid=1,
          if(session_length_min<=30,rate_cleaning_30_min,
            rate_cleaning_45_min) 
          ,0)
+
  if(typeid=2,rate_kitchenwork,0)
+
 if(typeid=3,rate_dogwalking,0)
) as amount_to_pay

from
tSessions
left join tUsers on tSessions.userid=tUsers.id
group by tUsers.Name

But your base scheme is not good. What if you need add or delete task types. You should separate tUsers and Task's Prices. Just add new table tRates and modify tTypes:

    -----------tTypes----------------
    |id      |name
    ---------------------------------
    |1       |Cleaning 30 min
    ---------------------------------
    |2       |Kitchenwork
    ---------------------------------
    |3       |Dogwalking
    ---------------------------------
    |4       |Cleaning 45 min
    ---------------------------------

-----------tSessions--------
|id      |userid  |typeid  |
----------------------------
|1       |1       |1       |
----------------------------
|2       |1       |4       |
----------------------------
|3       |1       |2       |
----------------------------
|4       |2       |2       |
----------------------------


-----------tUsers-----------------
|id      |name  |
-----------------
|1       |Tom   |
-----------------
|2       |Joe   |
-----------------
|3       |Dave  |
-----------------


and add tRates table with USER<->TASKS rates:


-----------tRates--------
|id      |userid  |typeid  | rate   |
---------------------------------------
|1       |1       |1       |30      |
--------------------------------------
|2       |1       |2       |40      |
--------------------------------------
|3       |1       |3       |20      |
--------------------------------------
|4       |1       |4       |50      |
--------------------------------------
|5       |2       |1       |35      |
--------------------------------------
|6       |2       |2       |45      |
--------------------------------------
|7       |2       |3       |20      |
--------------------------------------
|8       |2       |4       |60      |
--------------------------------------
|9       |3       |1       |40      |
--------------------------------------
|10      |3       |2       |30      |
--------------------------------------
|11      |3       |3       |10      |
--------------------------------------
|12      |3       |4       |60      |
--------------------------------------