COUNT of values in a column

Question

Post reply

COUNT of values in a column

inevercheckthis2002

SSCommitted

Points: 1819
More actions
April 7, 2014 at 10:06 am

#396557

Create table:
CREATE TABLE visited
(
page nvarchar(80),
username nvarchar(80),
);
Insert values:
INSERT INTO visited
VALUES
(N'Home', N'Kim'),
(N'Contact', N'Kim'),
(N'Products', N'Kim'),
(N'Home', N'Joe'),
(N'Contact', N'Joe'),
(N'Home', N'Pam'),
(N'Home', N'Sam'),
(N'Contact', N'Sam'),
(N'Products', N'Sam'),
(N'Home', N'Ray'),
(N'Products', N'Ray),
(N'Home', N'Kim'),
(N'Home', N'Pam'),
(N'Home', N'Sam')
;
(Actually, this did not work, I had to insert each row one at a time. I'd like to know why..)
Table:
SELECT * FROM VISITED
page,username
Home,Kim
Contact,Kim
Products,Kim
Home,Joe
Contact,Joe
Home,Pam
Home,Sam
Contact,Sam
Products,Sam
Home,Ray
Products,Ray
Home,Kim
Home,Pam
Home,Sam
(14 row(s) affected)
What I am wanting:
page, count
Contact, 3
Home, 5
Products, 3
I can get each count, if I know the page, but I won't always know which pages to count.
SELECT count (*)
from
(
SELECT DISTINCT page, username
from visited
where (page = N'Contact')
GROUP BY page, username
) a
3
(1 row(s) affected)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Your insert failed because in the line below, you're missing a quote after Ray:

(N'Products', N'Ray),

To get the count of each row:

select page, COUNT(*) row_count

from visited

group by page;

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You might be overcomplicating yourself. Grouping by page will give you one row per page value. Using COUNT(DISTINCT column) will count for each unique value.

You'll end up with a code as simple as this:

SELECT page,

COUNT(DISTINCT username) usercount

FROM visited

GROUP BY page

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

inevercheckthis2002 (4/7/2014)
Insert values:
INSERT INTO visited
VALUES
(N'Home', N'Kim'),
(N'Contact', N'Kim'),
(N'Products', N'Kim'),
(N'Home', N'Joe'),
(N'Contact', N'Joe'),
(N'Home', N'Pam'),
(N'Home', N'Sam'),
(N'Contact', N'Sam'),
(N'Products', N'Sam'),
(N'Home', N'Ray'),
(N'Products', N'Ray),
(N'Home', N'Kim'),
(N'Home', N'Pam'),
(N'Home', N'Sam')
;
(Actually, this did not work, I had to insert each row one at a time. I'd like to know why..)
[/code]

Just missing an apostrophe

(N'Products', N'Ray),

😎