SQL help

need query help

[SteveT]

[edit, found suitable queries to do what I want, now want to streamline them]
how to I combine the follow sql queries:
select distinct cuisine, count(cuisine) from business group by cuisine;
select distinct suburb, count(suburb) from business group by suburb;
select count(ID) from business

Table Business, ID is the primary key, and there are various combination of different cuisines/suburbs in each row.

Thanks.

[coopster]

Welcome to WebmasterWorld, SteveT!

I don't think you are going to be able to combine your statements. DISTINCT means exactly that. Consider the example snippet of a table in your database:

+---------+--------+ 
¦ cuisine ¦ suburb ¦ 
+---------+--------+ 
¦ chinese ¦ subone ¦ 
¦ chinese ¦ subtwo ¦ 
¦ chinese ¦ subsix ¦ 
¦ italian ¦ subone ¦ 
¦ italian ¦ subtwo ¦ 
+---------+--------+

If you

SELECT DISTINCT cuisine FROM table

it is going to return 2 rows and a

SELECT DISTINCT suburb FROM table

will return 3 rows.

If you

SELECT DISTINCT cuisine, suburb FROM table

it is going to return all five rows, because of the unique combinations.

[wackal]

couldn't you do something like this:

select count(select distinct cuisine from business group by cuisine), count(select distinct suburb from business group by suburb), count(select id from business) from business;

[danieljean]

Unless you explain what exactly you are trying to find out and what database you're running this on, it's hard to give a good answer.

Also, I'm wondering if the data is at all normalized. Can you have:

+---------+--------+---+ 
¦ cuisine ¦ suburb ¦ID ¦ 
+---------+--------+---+ 
¦ chinese ¦ subone ¦ 1 ¦ 
¦ italian ¦ subone ¦ 1 ¦ 
¦ chinese ¦ subtwo ¦ 2 ¦ 
+---------+--------+---+

If so, it's going to be hairy!

[SteveT]

Thanks for the details guys. I see what you mean Wackal, and I'm working on trying multiple selects within the same query (using mysql so have to apply to that).

The data is normalised w auto-incrementing id fields.

Danieljean, the aim of the query was to get the data required to fill in a 'summary of results'.
eg: Your search found:
Chinese (3)
Italian (2)
subone (2)
subtwo (2)
subsix (1) (see coopster's table data)
as in ignoring the relations between cuisine and suburb, simply returning the basic count of each distinct value.

Logically, it makes sense to run separate queries - but wanted to minimise no. queries I am running.

Thanks.

[danieljean]

Steve, unless you are running the latest MySQL, you may not be able to run the sub-queries as wackal suggested- though that would be the proper way of proceeding.

(Did I ever mention here on WW that I dislike MySQL?)

Anyhow, keep in mind that "premature optimization is the root of all evil", and try first doing the multiple, simple queries. For all its faults, MySQL is at least fast :)

[SteveT]

Yeah, might just go with the multiple searches :)

[m_shroom]

Now that you know what data you want.

Consider modifing your db to accomadate beter querys

I rewrote a 5 min set of querys down to 90 sec.

Then after a lot of thought modified my db turned the set into a 3 sec query