0
SELECT ENTERED_COUNT, MINUTES, USER, DATE_ENTERED ,
(SELECT SUM(ENTERED_COUNT * MINUTES) 
FROM tbl_coding 
WHERE USER = 111 
AND DATE_ENTERED >= '2018-03-01' AND DATE_ENTERED <='2019-03-09' GROUP BY DATE_ENTERED ) AS ECNT ,
(SELECT SUM(ENTERED_COUNT * MINUTES)  
FROM tbl_coding 
WHERE USER = 198 
AND DATE_ENTERED >= '2018-03-01' AND DATE_ENTERED <='2019-03-09' GROUP BY DATE_ENTERED) AS ECNT1,
(SELECT SUM(ENTERED_COUNT * MINUTES)  
FROM tbl_coding 
WHERE USER = 360 
AND DATE_ENTERED >= '2018-03-01' AND DATE_ENTERED <='2019-03-09' GROUP BY DATE_ENTERED) AS ECNT2 
FROM tbl_coding 
WHERE DATE_ENTERED >= '2018-03-01' AND DATE_ENTERED <='2019-03-09'
GROUP BY DATE_ENTERED
ORDER BY DATE_ENTERED DESC

when am executing above query am getting error like:

1242 - Subquery returns more than 1 row

actually am trying to achieve something like below:

DATE_ENTERED    ECNT    ECNT1   ECNT2
2018-03-01      214     125     15
2018-03-02      112     168     45
2018-03-03      113     95      99
2018-03-04      78      113     105
2018-03-05      55      96      83
2018-03-06      96      84      69
2018-03-07      111     75      86
2018-03-08      106     221     45
2018-03-09      189     118    311
Community
  • 1
  • 1
Gokul Vasan T
  • 1
  • Post a sample data set that relates to this data please – Matt Mar 20 '18 at 10:22
  • 2
    The sub-query shouldn't return more than one row - use a join instead. Since you are grouping by the date and selecting a range, of course it returns multiple rows. – Sloan Thrasher Mar 20 '18 at 10:26
  • hi for above query can you send me join query example, I tried to use join.. but I didn;t get expecting output..Thank you – Gokul Vasan T Mar 20 '18 at 10:30
  • This transformation is called pivoting and has been asked and answered here on SO soooo many times. Pls note while the duplicate topic does provide a MySQL based solution to the problem, it may be more efficient to perform this transformation in the application logic. – Shadow Mar 20 '18 at 10:30
  • No, you do need to join anything. The subqueries and the outer query all select from the same table. – Shadow Mar 20 '18 at 10:32
  • @Shadow '..not..' – Strawberry Mar 20 '18 at 10:36
  • @Strawberry thanks :) – Shadow Mar 20 '18 at 10:48

0 Answers0