Select more than one row inside sub query or evade sub query

Question

I would like to know how to realize query which selects sum of time column grouped by clientid column WHERE clause is divided by months and department from the same table:

table is the following:

The goal here is to have final result like the following:

result 1.0

------------------------------
clientid | december| february
------------------------------
1        | 2:20    | 4:00
------------------------------
2        | 4:00    | 7:00
------------------------------

Result will be displayed in the following manner:

<table>
  <thead>
    <tr>
      <th>Clientes</th>
      <th>January</th>
      <th>February</th>                                                         
    </tr>
</thead>
<tbody>
<?php foreach($stmt as $row)
{   
echo"
<tr>
  <td>".$row['clientid']."</td>
  <td>".$row['january']."</td>
  <td>".$row['february']."</td>                               
</tr>"; }; ?>              
</tbody>
</table>

In the following case scenario of query:

SELECT t.clientid, t.department, 
                        (SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `time` ) ) ) FROM time_management WHERE YEAR(date)='$year' AND MONTH(date)= 12 AND department = '$department' GROUP by clientid  ) as january,
                        (SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `time` ) ) ) FROM time_management WHERE YEAR(date)='$year' AND MONTH(date)= 2 AND department = '$department' GROUP by clientid ) as february
                        FROM time_management t WHERE t.department = '$department' GROUP by t.clientid

I get error: SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row

Since the sub query's are returning more than one row.

Without GROUP BY inside the sub query's the result is the following:

1   06:20:00    11:00:00                                            
2   06:20:00    11:00:00

So my question is how to structure the query in order to get the desired result showed in result 1.0

Any help will be very welcome. Thank you!

How do you want to handle when you have multiple years? all Dec get setup as Jan and all feb as feb even spanning years? — xQbert, Dec 09 '16 at 14:20
Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Jiri Tousek, Dec 09 '16 at 14:20
What you're trying to do is called "pivot table". Unfortunately MySQL doesn't seem to have built-in support for that (unlike some other DBs), the linked question has some info about how to work around PIVOT being missing. — Jiri Tousek, Dec 09 '16 at 14:23

Giorgos Betsos · Accepted Answer · 2016-12-09T14:24:03.487

1

There is no need to use subqueries. You can use conditional aggregation:

SELECT SUM( CASE WHEN MONTH(`date`) = 2 THEN TIME_TO_SEC( `time` )  ELSE 0 END) AS February,
       SUM( CASE WHEN MONTH(`date`) = 12 THEN TIME_TO_SEC( `time` )  ELSE 0 END) AS December
FROM time_management
WHERE t.department = '$department'
GROUP BY clientid

This way you can calculate the SUM of time for each month of the year.

edited Dec 09 '16 at 14:24

answered Dec 09 '16 at 14:17

Giorgos Betsos

71,379
9
63
98

do you have a typo in the first query? 2 instead of 12? or is this deliberate? – davejal Dec 09 '16 at 14:18
The idea is to select the 2 months at the same time. February and December. will that case select only one of the 2 months ? – John Siniger Dec 09 '16 at 14:20
@davejal `2` is for `February`, whereas `12` is for `December`. I have included these months because of the sample data provided in the OP – Giorgos Betsos Dec 09 '16 at 14:20
@JohnSiniger Each `CASE` calculates the `SUM` for one month. You can include `12` separate `CASES` in order to calculate the `SUM` for every month. – Giorgos Betsos Dec 09 '16 at 14:21
This seems to be working very good, any hint by the way how to visualize the sum of the time as time ? how it shows as seconds – John Siniger Dec 09 '16 at 14:28
1

@JohnSiniger use `SEC_TO_TIME` – Giorgos Betsos Dec 09 '16 at 14:39

score 1 · Answer 2 · edited Dec 09 '16 at 14:49

1

Try using this:

SELECT SUM( CASE WHEN MONTH(`date`) = 2 THEN TIME_TO_SEC( `time` )  ELSE 0 END) AS February,
       SUM( CASE WHEN MONTH(`date`) = 12 THEN TIME_TO_SEC( `time` )  ELSE 0 END) AS December
FROM time_management
WHERE t.department = '$department'
GROUP BY clientid

edited Dec 09 '16 at 14:49

Zachary Craig

2,192
4
23
34

answered Dec 09 '16 at 14:29

Piyush Mehta

13
4

Select more than one row inside sub query or evade sub query

2 Answers2