CODEWITHSUNDEEP
sqlteradata
craigtb
craigtb

Reputation: 667

Getting a Count of Users by Date

I am trying to create a query that will return results for the number of distinct users who have accessed something by date. Right now I have a query that will display 2 columns, the first being date and the second being user name. It will list all the distinct users who accessed the application on a certain date but they will each have their own distinct row. Here is the query that does that:

SELECT DISTINCT logdate, User AS ReportUser
FROM table          
WHERE appname='abcd1234' AND logdate >=DATE-30

I have tried putting COUNT() around User but it says selected non-aggregate values must be part of the associated group.

Any idea how I can get this query to just show like row for the past 30 days and the count of distinct users?

Upvotes: 1

Views: 2418

Answers (2)

Raj
Raj

Reputation: 22926

This will be the right approach for that.

SELECT logdate, Count(User) AS ReportUser
FROM table          
WHERE appname='abcd1234' AND logdate >=DATE-30
GROUP BY 1

Never use DINTINCT in Teradata. It always slows down your query performance. Use GROUP BYinstead.

CORRECTION In Teradata 13 the optimizer is able to determines which version is more efficient, based on statistics. This can be found in the Release Summary for Teradata 13 under "Group By and DISTINCT Performance Equivalence". - http://www.info.teradata.com/edownload.cfm?itemid=083440012 (PDF)

Upvotes: 1

Andrew
Andrew

Reputation: 7768

use "GROUP BY" after WHERE clause

SELECT logdate, COUNT (User) AS ReportUser 
FROM table           
WHERE appname='abcd1234' AND logdate >=DATE-30 
GROUP BY logdate

Upvotes: 0

Related Questions