Join 3,512 readers in helping fund MetaFilter (Hide)


mySql query assistance, please!
September 5, 2010 12:49 PM   Subscribe

mySQL query question about calculating percentages in a table.

I have an array of fixed values (let's call it [Val0,Val1,Val2]) and a table with one of its columns containing one of the values from the array in it.

|Col1|
-------
Val1
Val0
Val1
Val2
Val1
Val1


This table is constantly having more data shoved into it, and all the while, the data is one of the X values from the array (I've shown 3 here, but the actual number fluctuates up and down). The values in each row the table will *always* be one of the values from the array, be design.

How can I devise a query in mySQL/PHP that will print out an on-the-fly percentage breakdown by the specific values that are in the array? So for the above example, it would say that Val1=66%, Val0=16% and Val2=16%.

Bonus question: The values in the table are actually longer strings (URLs, to be specific) and the values in my array are the "www.example.com" portion of the URL, so I'll have to strip down each value in the table as I iterate through to compare, or I suppose I could use a substr comparison. Any tips on integrating a strip function into this whole lot would be appreciated (such as what would be better processing time-wise, to do it with a parse_url or a substr function), but I think I have that part figured out. I'm just trying to figure out the whole percentage calculation on-the-fly bit.

Thanks!
posted by omnipotentq to Computers & Internet (3 answers total) 1 user marked this as a favorite
 
You can do some minor postprocessing in PHP, right? I think the most straightforward way is to do a SELECT Col1, COUNT(...) FROM ... GROUP BY Col1; to get the counts of each value, then sum-and-divide in PHP to get percentages.
posted by hattifattener at 12:57 PM on September 5, 2010


I don't have access to a MySQL install right now, but this is one way to do it in MSSQL and can probably be adapted:
declare @total int
select @total = count(*) from table
select col1, count(col1), count(col1) * 100.0 / @total from table
group by col1

posted by sanko at 1:00 PM on September 5, 2010


Both of those are exactly what I'm looking for! Thanks very much for helping this mySQL noob out.
posted by omnipotentq at 1:11 PM on September 5, 2010


« Older Please help me identify the wh...   |  Memoir about small-house livin... Newer »
This thread is closed to new comments.