CODEWITHSUNDEEP
mysqlsql
user2058653
user2058653

Reputation: 733

Select multiple fields from subquery

I have the next query:

SELECT
    a.Date,
    (SELECT SUM(Used), SUM(Max) FROM Switch_Statistic b WHERE Date = (SELECT MAX(Date) FROM Switch_Statistic WHERE Switch_ID = b.Switch_ID AND Date <= a.Date))
FROM Switch_Statistic a
GROUP BY Date;

As you see I need to select SUM(Used), SUM(Max) from subquery. With CONCAT is not good solution!


Table schema:

ID --- Switch_ID --- Date --- Max --- Used

Some data:

1   641 2014-10-04  2   16
20  630 2014-10-04  1   7
24  634 2014-10-04  0   8
26  641 2014-10-06  2   16
32  641 2014-10-07  2   16
35  641 2014-10-08  3   16
39  641 2014-10-09  2   16
64  293 2014-10-10  1   22
...
557 38  2014-10-12  3   22
559 293 2014-10-12  1   22
563 294 2014-10-12  6   22
565 641 2014-10-12  2   16

What I need: Example with CONCAT_WS

mysql> SELECT
    a.Date,
    (SELECT CONCAT_WS('/', SUM(Used), SUM(Max)) FROM Switch_Statistic b WHERE Date = (SELECT MAX(Date) FROM Switch_Statistic WHERE Switch_ID = b.Switch_ID AND Date <= a.Date)) AS Result
FROM Switch_Statistic a
GROUP BY Date;
+------------+----------+
| Date       | Result   |
+------------+----------+
| 2014-10-04 | 3/31     |
| 2014-10-06 | 3/31     |
| 2014-10-07 | 3/31     |
| 2014-10-08 | 4/31     |
| 2014-10-09 | 3/31     |
| 2014-10-10 | 249/1587 |
| 2014-10-11 | 354/2147 |
| 2014-10-12 | 360/2185 |
+------------+----------+
8 rows in set (0.26 sec)

Query logic: 1) Select all date's from table 2) SUM - Used and Max for current date, if Switch_ID don't have record for this date, then select the last which exists in table

Link to sqlfiddle - http://sqlfiddle.com/#!2/c3d479

Upvotes: 0

Views: 238

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You should be able to do this with just aggregation and no subqueries or joins:

  SELECT date, sum(used) as used, sum(max) as max
  FROM switch_statistic ss
  where ss.date = (select max(date) from Switch_Statistics ss2 where ss2.Switch_id = ss.SwitchId
  GROUP BY ss.date;

EDIT:

You seem to want a cumulative sum. In MySQL, this is often best done using variables:

SELECT date, used, max, (@u := @u + used) as cumeused, @m := @m + max) as cumemax
fROM (SELECT date, sum(used) as used, sum(max) as max
      FROM switch_statistic ss
      GROUP BY ss.date
     ) ss CROSS JOIN
     (SELECT @u := 0, @m := 0) vars
ORDER BY date;

Upvotes: 1

Related Questions