Reputation: 119
SQL select and count same table
I'm making a newsletter tool with a table that stores e-mail address's user and email id opened, to calculate total and unique opened for every sent e-mail.
I'd like to show a detailed page with a distinct about e-mail address's user and counting times that the user opened the e-mail.
Example of db table:
email_id | user_id | user
----------------------------
1 | 1 | [email protected]
1 | 1 | [email protected]
1 | 1 | [email protected]
1 | 2 | [email protected]
1 | 3 | [email protected]
1 | 4 | [email protected]
1 | 4 | [email protected]
Web result:
║ User ║ Times ║
╬═══════════════════╬═══════╣
║ [email protected] ║ 3 ║
║ [email protected] ║ 1 ║
║ [email protected] ║ 1 ║
║ [email protected] ║ 2 ║
I tried the following query but it works only for the first row, then it truncates the rest:
SELECT DISTINCT user, count(DISTINCT user) as counttotal
FROM newsletter_log
where email_id = 1
Upvotes: 0
Views: 1429
Answers (4)
Reputation: 323
To include total:
SELECT email, IsNull(User, 'Total'), COUNT(User) [Count]
FROM newsletter_log
WHERE email = 1
GROUP BY email, User
WITH (ROLLUP)
ORDER BY COUNT(User) DESC
Upvotes: 1
Reputation: 1107
Concept is like, you want to get count based on user group. So consider user as group and do the count per group.
select user, count(1) as cnt
FROM newsletter_log
where email_id = 1
group by user
For Knowledge,you can study various count patterns like count(*),count(1) etc.
Upvotes: 1
Reputation: 538
try this.
select user,count(user) as times from newsletter_log where email_id = 1 group by user
you dont need to use distinct. just use group by.
Upvotes: 1
Reputation: 44766
Simply do a GROUP BY. Use COUNT(*) to count.
select user, count(*) as counttotal
FROM newsletter_log
where email_id = 1
group by user
Upvotes: 3