SQL Query two values for each record

Question

I'm trying to query a customers table to get the total number of accounts per rep grouped by whether they were created this year or before.

CUSTOMER NAME
ACCOUNT REP
DATE CREATED

The query I'm trying to return would look like.

REP | NEW_ACCOUNTS | OLD_ACCOUNTS | TOTAL
-----------------------------------------
Tom | 100          | 12           | 112
Ted | 15           | 1            | 16

The query I've written looks as follows.

SELECT        REP, CASE WHEN YEAR(GETDATE()) > YEAR(DATE_CREATED) THEN 1 ELSE 0 END AS ThisYear
FROM          CUSTOMERS
GROUP BY      REP, DATE_CREATED

Unfortunately, this is giving me

REP | ThisYear
-----------------------------------------
Tom | 1
Ted | 0
Tom | 0
Ted | 1
Ted | 1

You are close try Using Count() with current query for ex. SUM(CASE WHEN .....<your condition> as NEW_ACCOUNTS), SUM(CASE WHEN ....<condition flip for old records> as OLD_ACCOUNTS that should do it. — Mahesh
– Mahesh, Commented Jul 16, 2019 at 10:44

Gordon Linoff · Accepted Answer · 2019-07-16 10:47:06Z

1

I think you want conditional aggregation:

SELECT REP,
       SUM(CASE WHEN YEAR(GETDATE()) = YEAR(DATE_CREATED) THEN 1 ELSE 0 END) AS NEW_ACCOUNTS,
       SUM(CASE WHEN YEAR(GETDATE()) > YEAR(DATE_CREATED) THEN 1 ELSE 0 END) AS OLD_ACCOUNTS,
       COUNT(*) as TOTAL
FROM CUSTOMERS
GROUP BY REP;

This assumes that creation dates are not in the future -- a reasonable assumption.

If you want one row per REP, then the only column in the GROUP BY should be REP.

answered Jul 16, 2019 at 10:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh Sharma · Accepted Answer · 2019-07-16 11:03:14Z

1

You can want conditional aggregation :

SELECT REP, 
       SUM(CASE WHEN YEAR(GETDATE()) = YEAR(DATE_CREATED) THEN 1 ELSE 0 END) AS NEW_ACCOUNTS,            
       SUM(CASE WHEN YEAR(GETDATE()) > YEAR(DATE_CREATED) THEN 1 ELSE 0 END) AS OLD_ACCOUNTS, COUNT(*) AS TOTAL       
FROM CUSTOMERS
GROUP BY REP;

edited Jul 16, 2019 at 11:03

answered Jul 16, 2019 at 10:47

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

1 Comment

B.Muthamizhselvi Over a year ago

use this condition YEAR(GETDATE()) > YEAR(DATE_CREATED), If use '<' it gives 0 result

Collectives™ on Stack Overflow

SQL Query two values for each record

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related