COUNT

  • Thread starter Thread starter George Stevenson
  • Start date Start date
G

George Stevenson

I've got a table of property with owner names that are in random order. 1
owner can own multiple properties.

I'd like create a list of owners with the count of the number of properties
for each owner.

I'm struggling with the SQL Select to do this, could someone help me out?

The following select statment only lists the single number of owners, not a
list of owners and the number of properties for each one.

SELECT count(*)
FROM [SELECT proptest.Owner1 FROM proptest ORDER BY owner1]. AS owner1;
 
I've got a table of property with owner names that are in random order. 1
owner can own multiple properties.

I'd like create a list of owners with the count of the number of properties
for each owner.

I'm struggling with the SQL Select to do this, could someone help me out?

The following select statment only lists the single number of owners, not a
list of owners and the number of properties for each one.

SELECT count(*)
FROM [SELECT proptest.Owner1 FROM proptest ORDER BY owner1]. AS owner1;
Click to expand...

You're making it harder than it needs to be!

SELECT Owner1, Count(*)
FROM proptest
GROUP BY Owner1;
 
Thanks John. Since posting I was able to crack it. Here's my solution.
This provides exactly what I needed.

SELECT *
FROM [SELECT owner1, count(owner1) AS OwnerCount FROM property GROUP BY
owner1]. AS [%$##@_Alias]
WHERE ownercount > 2
ORDER BY OwnerCount DESC;

This gives me a list with highest count property owners at the top with more
than 2 properties.

George


John Vinson said:
I've got a table of property with owner names that are in random order. 1
owner can own multiple properties.

I'd like create a list of owners with the count of the number of properties
for each owner.

I'm struggling with the SQL Select to do this, could someone help me out?

The following select statment only lists the single number of owners, not a
list of owners and the number of properties for each one.

SELECT count(*)
FROM [SELECT proptest.Owner1 FROM proptest ORDER BY owner1]. AS owner1;
Click to expand...

You're making it harder than it needs to be!

SELECT Owner1, Count(*)
FROM proptest
GROUP BY Owner1;
Click to expand...
 
Thanks John. Since posting I was able to crack it. Here's my solution.
This provides exactly what I needed.

SELECT *
FROM [SELECT owner1, count(owner1) AS OwnerCount FROM property GROUP BY
owner1]. AS [%$##@_Alias]
WHERE ownercount > 2
ORDER BY OwnerCount DESC;

This gives me a list with highest count property owners at the top with more
than 2 properties.
Click to expand...

The subquery is still not necessary.

Select Owner1, Count(*)
FROM Property
GROUP BY Owner1
HAVING Count(*) > 2
ORDER BY Count(*) Desc;
 
----- John Vinson wrote: -----

Thanks John. Since posting I was able to crack it. Here's my solution.
This provides exactly what I needed.
SELECT *
Click to expand...
FROM [SELECT owner1, count(owner1) AS OwnerCount FROM property GROUP BY
owner1]. AS [%$##@_Alias]
WHERE ownercount > 2
ORDER BY OwnerCount DESC;
This gives me a list with highest count property owners at the top with more
Click to expand...
than 2 properties.
Click to expand...

The subquery is still not necessary.

Select Owner1, Count(*)
FROM Property
GROUP BY Owner1
HAVING Count(*) > 2
ORDER BY Count(*) Desc;





John,

I have a similar problem,

I have multiple boolean fields (-1 and 0) and a machine field
I want to group on the machine field and count the number of true instances in every field

Martin
 
SELECT MachineField,
Abs(Sum(BooleanA)) AS CountBooleanA,
Abs(Sum(BooleanB)) AS CountBooleanB,
Abs(Sum(BooleanC)) AS CountBooleanC
FROM SomeTable
GROUP BY MachineField

----- John Vinson wrote: -----

Thanks John. Since posting I was able to crack it. Here's my solution.
This provides exactly what I needed.
SELECT *
Click to expand...
FROM [SELECT owner1, count(owner1) AS OwnerCount FROM property GROUP BY
owner1]. AS [%$##@_Alias]
WHERE ownercount > 2
ORDER BY OwnerCount DESC;
This gives me a list with highest count property owners at the top with more
Click to expand...
than 2 properties.
Click to expand...

The subquery is still not necessary.

Select Owner1, Count(*)
FROM Property
GROUP BY Owner1
HAVING Count(*) > 2
ORDER BY Count(*) Desc;





John,

I have a similar problem,

I have multiple boolean fields (-1 and 0) and a machine field
I want to group on the machine field and count the number of true instances in every field

Martin
Click to expand...
 
Back
Top