CODEWITHSUNDEEP
mysqlruby-on-rails
Ōkami X Oukarin
Ōkami X Oukarin

Reputation: 435

Select last column record with group_by

I have a table like this:

----------------------------------
| post | page_likes | created_at |
----------------------------------
|   2  |     700    | 2017-11-02 |
----------------------------------
|   1  |     702    | 2017-11-03 |
----------------------------------
|   4  |     709    | 2017-11-04 |
----------------------------------
|   2  |     712    | 2017-11-05 |
----------------------------------
|   0  |     710    | 2017-11-06 |

And a query to calculate data and use it for my chart

SELECT DATE_FORMAT(created_at, '%Y%m') as ymd_id,
                  page_likes AS page_likes,
                  sum(post) AS total_posts

                  FROM table
                    Where created_at BETWEEN '2017-11-02 00:00:00' AND '2017-11-6 00:00:00'
                  GROUP BY ymd_id
                  ORDER BY ymd_id ASC

Result:

---------------------------------------
|total_posts| page_likes |   ymd_id   |
---------------------------------------
|     9     |     700    |   201711   |
---------------------------------------

page_like goes wrong. It takes 700, but the value i want is 710.

Is there a way to modify the query above to get last row of page_like?

Upvotes: 0

Views: 58

Answers (3)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Another approach using string function

SELECT 
  DATE_FORMAT(created_at, '%Y%m') AS ymd_id,
  SUBSTRING_INDEX(GROUP_CONCAT(page_likes ORDER BY created_at DESC),',',1) AS page_likes,
  SUM(post) AS total_posts 
FROM
  demo 
WHERE created_at >= '2017-11-02 00:00:00' 
  AND created_at <= '2017-11-6 00:00:00' 
GROUP BY ymd_id 
ORDER BY ymd_id ASC

DEMO

Upvotes: 1

Faraz PV
Faraz PV

Reputation: 522

SELECT tt.ymd_id, test.page_likes, tt.total_posts FROM (
    SELECT DATE_FORMAT(created_at, '%Y%m') as ymd_id, MAX(created_at) as created_at, sum(post) AS total_posts
    FROM test
    Where created_at BETWEEN '2017-11-02 00:00:00' AND '2017-11-6 00:00:00'
    GROUP BY ymd_id
    ORDER BY ymd_id ASC) as tt 
join test on test.created_at = tt.created_at

SQL FIDDLE

http://sqlfiddle.com/#!9/b66e02/20

Upvotes: 2

Marcelo Ribeiro
Marcelo Ribeiro

Reputation: 1738

Try using 

select max(page_likes) as page_likes

in the select block

Upvotes: 1

Related Questions