Count query using 2 tables - 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 2008 Forums

Transact-SQL (2008)

Count query using 2 tables

Author

Topic

alstefani
Starting Member

2 Posts

Posted - 2009-10-26 : 19:47:15

Ok guys,

I'm new to this so please help me

Table 1:aMake
Fields: make_id, make_name

Table 2: autoads
Fields:ad_id,make_id,model_id,price,color etc.

Need to count records on Table 2 by make_id and display make_name

Like this:

make_name | CNT
Alfa Romeo 3
BMW 2

Practically I need to know how many ads I have per car make (do not show makes with 0 ads)

Any suggestions wil be appreciated

Albano

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-26 : 23:58:15

Select
make_name,count(1)
From
aMake, autoads
Where
aMake.make_id=autoads.make_id
Group by
make_name

alstefani
Starting Member

2 Posts

Posted - 2009-10-27 : 10:41:49

Hello,

Thank you for the reply

I also need the make_id on the results:

Like this:

make_name | make_id | CNT(count)

Alfa Romeo | 39 | 3

Do I need a join query?

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-28 : 00:41:52

Select
make_name,aMake.make_id,count(1)
From
aMake, autoads
Where
aMake.make_id=autoads.make_id
Group by
make_name,aMake.make_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources