[RESOLVED] MySQL Query Counting

NFClimber

Hi everyone,

I've been searching for this for awhile, and can't seem to find a solution. Hopefully someone can give me the name of a function, or a best practice idea.

What I'm trying to do is count the number of instances of some specific data, that I just realized, sometimes appears twice, or more, on the same row. Thus, when I use the standard mysql_num_rows($result) it gives me an innacurate number (Because it's counting the rows, not the number of values).

I guess I'm looking for a mysql_num_values type of function. Any help? 🙂

Roger_Ramjet

You use a group by aggregate function for this. You can both find out how many unique values there are and how many instances of each value there are as well.

If you just want a list of unique values then the DISTINCT keyword in your select is all that is required.

// just return one instance of each value
$sql = "SELECT DISTINCT col FROM table";
// num rows will give you the count

// count how many instances there are of each value
$sql = "SELECT col, COUNT(col) FROM table GROUP BY col";
// will return a list of values and how many instances of each there are
// again num rows will give the count of different values

NFClimber

I wasn't able to get that process to work...I'm not so sure that it's exactly what I'm looking for. Let me explain further...

My select string looks something like this -

SELECT * FROM table where (column1 = Football OR column1 = Basketball) OR (column2 = Football OR column2 = Basketball) OR (column3 = Football OR column3 = Basketball) ... etc. ... etc.

Needless to say, I'm thinking that I may need to use multiple selection strings of some kind. I'm selecting upwards of 8 different values from 5 different columns, for 7 different groupings (It's basically a table listing selections made by users for different times and programs). All of this data is located in one table. Any more suggestions?

Roger_Ramjet

Well, your first problem is that no matter what selection criteria you use, you are only going to get 1 count for a row with 2 matches, eg. football in cols2 and col4, or football in col2 and basketball in col3.

Strategies to overcome this vary according to what you are really after, sorry but that still is not quite clear.

Simplest solution is to use a UNION query

$sql = "SELECT 1 AS col, col1 AS sport, COUNT(col1) AS count FROM table WHERE col1='football' OR col1='basketball' GROUP BY col1
UNION
SELECT 2, col2, COUNT(col2) FROM table WHERE col2='football' OR col2='basketball' GROUP BY col2
UNION
SELECT 3, col3, COUNT(col3) FROM table WHERE col3='football' OR col3='basketball' GROUP BY col3"

That will give you individual counts for each column and they will look something like this

col | sport | count
1 | football | 20
1 | basketball | 36
2 | football | 18
2 | basketball | 5
3 | football | 13
3 | basketball | 47

Now, how you add those totals up depends on your version. If it supports sub-queries then wrap the Union query in another select that aggregates the values. In that case you can omit my column identifier called 'col' in the first select.

Otherwise you can use a temp table to store the reuslts and aggregate them from that.