INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Count one field within a distinct query on another?

Count one field within a distinct query on another?

Count one field within a distinct query on another?

(OP)
Help please I have table with two key fields varUser and varProperty - I am trying to count varProperty within a distinct varUser query to count varPropeerty only once for each user. SQL not my strongpoint and I can get a distinct or a count but not both.

RE: Count one field within a distinct query on another?

your question is unclear but here is a a query that will return you a list of users subcategorised by a list of their varProperties and a count of each. so if user 1 has recorded 3 properties one of which is red and the other two is blue, you'd get

CODE

user    property    count
1       blue        2
1       red         1 

CODE

SELECT      varUser,
            varProperty,
            count(varProperty) AS 'Num Instances'
FROM        tablename
GROUP BY    varUser ASC,
            varProperty ASC 

if you don't want the count, just omit the third line and suppress the trailing comma.
if you just want the count of the properties, omit the second line.
if you want something else, it's always helpful to provide a sample.

RE: Count one field within a distinct query on another?

(OP)
Hi and thank you so much but it is not quite what I am trying to do - I am trying to count how many users viewed ech property ignoring multiple viewings by a user. So for example

if user1 has viewed property1 x 2 property2 x 3 property3 x 4 and
user3 has viewed property1 x2 only the results of my querty should read

property1 2 viewings
property2 1 viewing
property3 1 viewing

Hope that makes sense.

RE: Count one field within a distinct query on another?

on your data the rows would look like this

CODE

varUser     varProperty
1           1
1           1
1           2
1           2
1           2
1           3
1           3
1           3
1           3
3           1
3           1 

you are trying to achieve how many unique users have viewed each property

CODE

SELECT      varProperty, COUNT(DISTINCT varUser)
FROM        tablename
GROUP BY    varProperty ASC 

RE: Count one field within a distinct query on another?

(OP)
Spot on - thank you so much

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close