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
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:59 AM
Points: 86, Visits: 369
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
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 4,241, Visits: 3,675
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: Yesterday @ 9:43 PM
Points: 3,783, Visits: 8,482
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.
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?

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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 2,217, Visits: 5,998
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