CODEWITHSUNDEEP
phpmysql
jonmrich
jonmrich

Reputation: 4323

Use declared "AS" variable in SQL query

I have the following query, which doesn't work:

$sql = "SELECT dma, COUNT(*) as dma_count, round(dma_count/32434 * 100) as dma_percent FROM {$table} where dma != '0' GROUP BY dma ORDER BY dma_count DESC;";

The reason (I know) it doesn't work is because I'm using dma_count in this part round(dma_count/32434 * 100).

What's the correct way to do this?

EDIT:

Additional challenge. Instead of using 32434, I want to use a variable. I get the variable like this:

$get_total = "SELECT count(DISTINCT `exuid`) from {$table};";
$total = $dbh->query($get_total)->fetchAll(PDO::FETCH_ASSOC);

so my query becomes (with the fix recommended in the comments)

$sql = "SELECT dma, COUNT(*) as dma_count, round(COUNT(*)/{$total} * 100) as dma_percent FROM {$table} where dma != '0' GROUP BY dma ORDER BY dma_count DESC;";

This doesn't work because I think $total is in the wrong format. How can I fix this?

EDIT AGAIN:

Got it! $total is just the count of my rows, so I have this instead.

SELECT dma, COUNT(*) as dma_count, round(COUNT(*)/(SELECT COUNT(*) FROM {$table}) * 100,2) as dma_percent FROM {$table} where dma != '0' GROUP BY dma ORDER BY dma_count DESC;"

Upvotes: 1

Views: 69

Answers (1)

user557846
user557846

Reputation:

you can use COUNT(*) in the equation instead of the alias, or the finial answer a sub query. In general avoid sub queries if you can use a join.

Upvotes: 1

Related Questions