Count one field within a distinct query on another? 1

[bradoirs]

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.

 

[jpadie]

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

user[tab]property[tab]count
1   [tab]blue    [tab]2
1   [tab]red     [tab]1

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.

 

[bradoirs]

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.

 

[jpadie]

on your data the rows would look like this

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

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

 

[bradoirs]

Spot on - thank you so much