May 14, 2013 at 8:35 am
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
May 14, 2013 at 8:50 am
Hi and welcome to the forums. For this query you will need to use COUNT and GROUP BY.
If you want more specific help with the actual code you will need to help us a little bit first. Basically we need to have something to work with. ddl (create table scripts) and sample data (insert statements) along with desired output based on the sample data. You do not need to post thousands of rows, just enough to demonstrate the issue at hand.
Please take a few minutes and read the first article referenced in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2013 at 8:54 am
Hi and welcome to the forum.
As you here first time, I would advice you to have a look an article from the link at the bottom of my signature. It explains the best way to ask questions here.
You can see if the question would be posted as per following, it would be easier for us to start helping you:
I have a database with estate agents and properties.
The properties are stored in table_ads:
create table #table_ads (id int, category varchar(10), userid int, ad_text varchar(50))insert #table_ads
select 45, 'flat', 236, 'flat for rent'
union select 46, 'house', 237, 'house for rent'
The estate agents are stored in table_ea:
create table #table_ea (id int, name varchar(50), username varchar(50), email varchar(50), password varchar(20))insert #table_ea
select 236, 'Robert', 'Royalestate', 'royalea@gmail.com', 'x564d5s6s'
union select 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
Any way. You need simple JOIN query:
select ea.username, COUNT(*) as qty
from #table_ea as ea
join #table_ads as ad on ad.userid = ea.id
group by ea.username
The above will display all users which has at least one property.
If you want to display users with zero properties, you will need to use OUTER JOIN and count ID's from table_ads table:
-- ad one more user
insert #table_ea select 238, 'Don', 'DonCarleone', 'DonCarleone@gmail.com', 'xxxxx'
select ea.username, COUNT(ad.id) as qty
from #table_ea as ea
left join #table_ads as ad on ad.userid = ea.id
group by ea.username
May 14, 2013 at 9:12 am
Thank you so much!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply