SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


COUNT of values in a column


COUNT of values in a column

Author
Message
inevercheckthis2002
inevercheckthis2002
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 474
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)



Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16532 Visits: 10059
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
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16375 Visits: 19076
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
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14992 Visits: 18592
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),


Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search