Select multiple fields from subquery

Question

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

Can you please share your tables structure, some sample data, and what you're trying to achieve? — Mureinik
– Mureinik, Commented Oct 12, 2014 at 17:22

Gordon Linoff · Accepted Answer · 2014-10-12 17:42:00Z

1

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;

edited Oct 12, 2014 at 17:42

answered Oct 12, 2014 at 17:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

user2058653 Over a year ago

cumeused, cumeused are shown me as null and result are also incorrect. Your query have a little bit wrong logic, look at my query.

Collectives™ on Stack Overflow

Select multiple fields from subquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related