CODEWITHSUNDEEP
sql
user9762321
user9762321

Reputation: 93

Grouping data by 7 days in SQL returning error

I'm wanting to group my data and count the number of transactions(FOCUS) for each 7 day period (Saturday-Friday). I think if I change getdate() to getdate(-2) that would do the Sat-Fri, however, I can't test it because I'm getting an error that states "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I tried moving the datediff to a GROUP BY and that didn't work either. Any advice? Thanks! 

SELECT COUNT(FOCUS), datediff(day, ENTRY_DATE, getdate())/7 AS WEEK
FROM ACCOUNTHISTORY
WHERE FOCUS = 6050
AND PRINCIPAL_AMT > 0
AND ENTRY_DATE >= '2020-01-01'

Upvotes: 0

Views: 33

Answers (2)

Sifat Haque
Sifat Haque

Reputation: 6067

You need to use group by in your query.

SELECT COUNT(FOCUS), datediff(day, ENTRY_DATE, getdate())/7 AS WEEK
FROM ACCOUNTHISTORY
WHERE FOCUS = 6050
    AND PRINCIPAL_AMT > 0
    AND ENTRY_DATE >= '2020-01-01'
GROUP BY WEEK;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You need to repeat the expression in the GROUP BY which needs to be added:

SELECT COUNT(*), datediff(day, ENTRY_DATE, getdate())/7 AS WEEK
FROM ACCOUNTHISTORY
WHERE FOCUS = 6050 AND
      PRINCIPAL_AMT > 0 AND
      ENTRY_DATE >= '2020-01-01'
GROUP BY datediff(day, ENTRY_DATE, getdate())/7;

Upvotes: 1

Related Questions