Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

COUNT of values in a column Expand / Collapse
Author
Message
Posted Monday, April 7, 2014 10:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:00 AM
Points: 74, Visits: 319
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)


Post #1559173
Posted Monday, April 7, 2014 10:16 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 3,997, Visits: 3,020
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
Post #1559176
Posted Monday, April 7, 2014 10:18 AM This worked for the OP Answer marked as solution


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 3,374, Visits: 7,301
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1559177
Posted Monday, April 7, 2014 11:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 1,352, Visits: 3,832
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),

Post #1559207
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse