SQL sort and last record

Question

this is my first post so pardon me if my question is not in it's appropriate places or tittle

I have a table like this

ID     DATE    Cat  VALUE  
-------------------------    
1   07/07/2018  A   100  
2   07/07/2018  A   200  
3   07/07/2018  B   300  
4   07/07/2018  B   400  
5   07/07/2018  C   500  
6   07/07/2018  C   600  
7   08/07/2018  A   700  
8   08/07/2018  A   800  
9   08/07/2018  B   900  
10  08/07/2018  B   110  
11  08/07/2018  C   120

I would like to return

distinct category, sum of value, last record of the category

something like this

Cat sumValue    lastrecord
--------------------------
A   1800        800
B   1710        110
C   1220        120

is it possible to do it in a single query

thanks

I am able to find the SUM

SELECT cat, SUM(value) FROM table GROUP BY cat;

and find the last ID (autonumber key) using MAX

SELECT MAX(ID), cat FROM table GROUP BY cat;

but i just can't get the value for the last record

Please give it a try yourself and share your attempts so far with the others. — dmitryro, Jul 07 '18 at 19:27
i've tried using select top 1 order by desc but I can't combine it with distinct — AlbertSan, Jul 07 '18 at 19:32
sumValue is Sum of [value] where [cat] = distinct [cat] lastrecord is the last record by date of particular [cat] — AlbertSan, Jul 07 '18 at 19:32
Try using `SUM`, `GROUP BY` for grouping - you need aggregates. google for aggregates in sql. — dmitryro, Jul 07 '18 at 19:33
Possible duplicate of [SUM values in SQL starting from a specific point in another table](https://stackoverflow.com/questions/42536762/sum-values-in-sql-starting-from-a-specific-point-in-another-table) — Ogunleye Olawale, Jul 07 '18 at 19:35
I can find the last date using MAX `select cat, sum of value, MAX(date) from table GROUP BY cat;` but i just can't get the value for the last record — AlbertSan, Jul 07 '18 at 19:40
You have to use `SUM(Cat)` .... `GROUP BY Cat` to get the group sum — dmitryro, Jul 07 '18 at 19:46
Look at https://stackoverflow.com/questions/3491329/group-by-with-maxdate — dmitryro, Jul 07 '18 at 20:13

score 1 · Answer 1 · answered Jul 07 '18 at 19:48

This should do it

declare @t table (id int, cat char, value int);
insert into @t values 
(1, 'A', 100), 
(2, 'A', 200),   
(3, 'B', 300),   
(4, 'B', 400),   
(5, 'C', 500),   
(6, 'C', 600),   
(7, 'A', 700),   
(8, 'A', 800),   
(9, 'B', 900),   
(10, 'B', 110),   
(11, 'C', 120);  
select cat, value, sum  
from 
( select *
       , sum(value) over (partition by cat) as sum 
       , ROW_NUMBER() over (partition by cat order by id desc) as rn 
  from @t
) tt 
where tt.rn = 1

Kirtan Khatsuriya · Answer 2 · 2018-07-07T20:19:26.520

1

I hope you're looking for something like this,

Please replace the table name with your table name.

SELECT A.id, 
       A.cat, 
       A.date, 
       A.total_value, 
       A1.value 
FROM   (SELECT Max(id)    AS id, 
               cat, 
               Max(date)  AS Date, 
               Sum(value) AS Total_Value 
        FROM   tbl_sof 
        GROUP  BY cat) AS A 
       INNER JOIN tbl_sof A1 
               ON A.id = A1.id

edited Jul 07 '18 at 20:19

answered Jul 07 '18 at 20:09

Kirtan Khatsuriya

11
2

Thank you so much, this really help haven't really thought of using nested which I should – AlbertSan Jul 07 '18 at 20:32

SirPilan · Accepted Answer · 2018-07-07T21:10:07.437

SQLFiddle

SELECT
  t.cat,
  SUM(t.value) as sumValue,
  (
    SELECT
      t3.value
    FROM
      `table` t3
    WHERE
      t3.id = MAX(t2.id)
  ) as lastrecord
FROM
  `table` t
  JOIN
    `table` t2 ON t.id = t2.id
GROUP BY
  cat

EDIT shorter Version:

SELECT
    t.cat,
    SUM(t.value) as sumValue,
    (SELECT value FROM `table` t2 WHERE t2.id = MAX(t.id)) lastValue
FROM
    `table` t
GROUP BY
    t.cat

SQL sort and last record

3 Answers3