CODEWITHSUNDEEP
sql
Banny
Banny

Reputation: 841

Returning a result from a query, then a record count of that specific result

The title is slightly confusing, but basically, I have created a search feature which allows my users to enter search terms. Say if they entered 'e' it would return answers such as "Leeds, Manchester, Liverpool, Doncaster" etc. Basically, I want something that then checks each of those towns/cities one by one, checks how many bands are registered from those towns/cities, and display say:

** "Leeds - 5 records found"**

It would be easy if you could output multiple queries at once, however, you can't. If somebody knows a way around this I would be very grateful.

Upvotes: 1

Views: 73

Answers (2)

Jess
Jess

Reputation: 25137

Are you talking about something like this?

select town, count(*) as num
from something
where blah = blah
group by town

This should give you a results set like:

town      num
----      ----
Leeds     2
Liverpool 3

When you use aggregate functions like max, count and min and a group by clause, the aggregation applies to the group, in this case, the town.

Upvotes: 2

Balazs Gunics
Balazs Gunics

Reputation: 2077

Try something like this:

CREATE TABLE citiesWithBand AS
(Select city, count(band) as bandcount
From yourtable
group by city);

Then you can do queries:

SELECT city, bandcount
FROM citiesWithBand
WHERE city likes '%userinput%';

Or you can combine these into a single query:

Select city, count(band) as bandcount
From yourtable
WHERE city LIKES '%userinput%'
GROUP BY city;

Upvotes: 1

Related Questions