Counter sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Counter sql

Author

Topic

nino59
Starting Member

3 Posts

Posted - 2013-05-14 : 10:33:41

Hi,

My boss ask me some information: How many properties has each estate agent in our database. I need your help please.

I have a database with estate agents and properties.
The properties are stored in table_ads:
id category userid ad_text
45 flat 236 flat for rent
46 house 237 house for rent
The estate agents are stored in table_ea:
id name username email password
236 Robert Royalestate royalea@gmail.com x564d5s6s
237 David Boboestate boboea@gmail.com x5s4yhhs6s

I would like to get a list of estate agents with the quantity of properties they have

username qty
Royalestate 18
Boboestate 26
etc...

So I need to creqate an SQL query which will count the quantity of each estate agent, display this quantity with the username.

Can you help me please?

Regards

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-14 : 10:35:12

Could you supply the DDL of the tables - so we can understand the relationships of the tables, or is there just one table?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

nino59
Starting Member

3 Posts

Posted - 2013-05-14 : 10:48:14

Thanks for this quick answer.

The relation is easy.

table_ads is connected to table_ea by
userid id

ps:sorry but for the form, the forum remove spaces.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-14 : 12:41:41

[code]SELECT ea.name ,
COUNT(DISTINCT ad.id) AS Properties
FROM table_ea ea
LEFT JOIN table_ads ad ON ad.userid = ea.id
GROUP BY ea.name;[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources