4
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 LatestActivity_0_Logtime,
  NULL AS LatestActivity_1_Logtime
 FROM Table_1
 GROUP BY NAME
 UNION
 SELECT NULL,NULL,NULL,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

Then result is again undesirable as below

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

Please Help to get distinct & merged result as desired

7

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

  • Wow, I tried using function which was obviously not optimal. Great,thanks – Hina Khuman Dec 17 '18 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.