query string error in asp.net

Question

I have a problem in query.

Error is: {"Invalid column name 'TotalRecords'."}

I have a table named upload_news in this table there many records and I want retrieve data by country wise where in the distinct countries greater than 20 records.

select count(Distinct country) AS TotalRecords, country from upload_news where TotalRecords > 20";

Donal · Accepted Answer · 2014-03-25 11:36:21Z

2

You need to use Group By and Having:

select count(Distinct country) AS TotalRecords, country from upload_news 
group by country
having count(Distinct country) > 20

answered Mar 25, 2014 at 11:36

Donal

33.2k10 gold badges69 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vignesh Kumar A · Accepted Answer · 2014-03-25 11:46:22Z

1

Try like this

"SELECT *
FROM
(
select count(Distinct country) AS TotalRecords, country from upload_news 
group by country
) T
where TotalRecords > 20";

because TotalRecords alias column, you can't access it directly.

edited Mar 25, 2014 at 11:46

answered Mar 25, 2014 at 11:32

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

1 Comment

Sidra Kanwal Over a year ago

{"Column 'upload_news.country' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."}

Collectives™ on Stack Overflow

query string error in asp.net

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related