Find the number of entries - 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

General SQL Server Forums

New to SQL Server Programming

Find the number of entries

Author

Topic

Mufleeh
Starting Member

4 Posts

Posted - 2011-08-25 : 04:21:51

Hi,

I am new to this forum. Hope you all can help me to figure out my problem.

I am having a schema which has tables namely CustomerTable, LoansTable, AccountsTable etc. A customer is allowed to have many loans.

Now I want to find the customer_id who is having the highest number of loans in LoansTable.

I tried to make it using below query referring a forum which doesn't work.

SELECT MAX(cnt) FROM (SELECT zone_name, count(zone_name) cnt FROM view_zone_division_district_province GROUP BY zone_name);

Thanks in advance.

Regards,
Mufleeh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 04:56:20

[code]SELECT TOP 1 customer_id,
COUNT(loan_it) as loancnt
FROM CustomerTable ct
JOIN LoansTable lt
ON lt.customer_id = ct.customer_id
group by customer_id
ORDER BY loancnt DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mufleeh
Starting Member

4 Posts

Posted - 2011-08-25 : 05:13:13

Hi, Thanks for your response. The server gives me the below error.

ERROR: syntax error at or near "40"

Any idea about this?

quote:
Originally posted by visakh16

SELECT TOP 1 customer_id,
COUNT(loan_it) as loancnt
FROM CustomerTable ct
JOIN LoansTable lt
ON lt.customer_id = ct.customer_id
group by customer_id
ORDER BY loancnt DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 05:41:25

are you using sql server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mufleeh
Starting Member

4 Posts

Posted - 2011-08-25 : 11:49:58

It's pgsql....WAPP installed in local pc.

quote:
Originally posted by visakh16

are you using sql server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 12:38:29

sorry then you're in wrong forum. This is MS SQL SERver forum, please try your luck @ www.dbforums.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mufleeh
Starting Member

4 Posts

Posted - 2011-08-25 : 13:40:25

Extremely sorry if any inconvenience occurred.

quote:
Originally posted by visakh16

sorry then you're in wrong forum. This is MS SQL SERver forum, please try your luck @ www.dbforums.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources