snippetsqlMinor

How to get sum & latest activity in same result?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

resultsamelatesthowgetsumactivity

Problem

Table_1
Name   |   Activity  | LogTime
A      |    0        | 2018-12-17 10:16:04.877 
A      |    1        | 2018-12-15 10:16:04.877
A      |    0        | 2018-12-16 10:16:04.877
A      |    0        | 2018-12-10 10:16:04.877
A      |    0        | 2018-12-10 10:10:04.877
B      |    1        | 2018-12-16 10:16:04.877
B      |    0        | 2018-12-17 10:16:04.877
C      |    1        | 2018-12-14 10:16:04.877
C      |    1        | 2018-12-12 10:16:04.877
C      |    1        | 2018-12-18 10:16:04.877

Desired Result

Name |   TOTALActivity_0  | TOTALActivity_1  |  LatestActivity_0_Logtime  | LatestActivity_1_Logtime
A    |      4             |     1            |  2018-12-17 10:16:04.877   |  2018-12-15 10:16:04.877
B    |      1             |     1            |  2018-12-17 10:16:04.877   |  2018-12-16 10:16:04.877
C    |      0             |     3            |  NULL                      |  2018-12-18 10:16:04.877

I am able to get result as below

Name |   TOTALActivity_0  | TOTALActivity_1
A    |      4             |     1          
B    |      1             |     1          
C    |      0             |     3

Using below query

SELECT 
  NAME, 
  SUM(CASE WHEN Activity = 0 THEN 1 ELSE 0 END) TOTALActivity_0,
  SUM(CASE WHEN Activity = 1 THEN 1 ELSE 0 END) TOTALActivity_1
 FROM Table_1
 GROUP BY NAME

If I tried as below

SELECT 
  NAME, 
  SUM(CASE WHEN Activity = 0 THEN 1 ELSE 0 END) TOTALActivity_0,
  SUM(CASE WHEN Activity = 1 THEN 1 ELSE 0 END) TOTALActivity_1,
  CASE WHEN Activity = 0 THEN LogTime ELSE NULL END AS LatestActivity_0_Logtime,
  CASE WHEN Activity = 1 THEN LogTime ELSE NULL END AS LatestActivity_1_Logtime
 FROM Table_1
 GROUP BY NAME,Activity,LogTime

Then it is giving Single-single & redundant records.
Then I tried with again with below query

```
SELECT
NAME,
SUM(CASE WHEN Activity = 0 THEN 1 ELSE 0 END) TOTALActivity_0,
SUM(CASE WHEN Activity = 1 THEN 1 ELSE 0 END) TOTALActivity_1,
NULL AS

Solution

This should work:

SELECT 
    Name, 
    COUNT(CASE WHEN Activity = 0 THEN 1 END) AS TOTALActivity_0,
    COUNT(CASE WHEN Activity = 1 THEN 1 END) AS TOTALActivity_1,
    MAX(CASE WHEN Activity = 0 THEN LogTime END) AS LatestActivity_0_Logtime,
    MAX(CASE WHEN Activity = 1 THEN LogTime END) AS LatestActivity_1_Logtime
FROM
    Table_1
GROUP BY
    Name ;

Test in: dbfiddle.uk

Code Snippets

SELECT 
    Name, 
    COUNT(CASE WHEN Activity = 0 THEN 1 END) AS TOTALActivity_0,
    COUNT(CASE WHEN Activity = 1 THEN 1 END) AS TOTALActivity_1,
    MAX(CASE WHEN Activity = 0 THEN LogTime END) AS LatestActivity_0_Logtime,
    MAX(CASE WHEN Activity = 1 THEN LogTime END) AS LatestActivity_1_Logtime
FROM
    Table_1
GROUP BY
    Name ;

Context

StackExchange Database Administrators Q#225145, answer score: 7

Revisions (0)

No revisions yet.