Query sum to columns (Double Query)

[kproc]

Hi

I'm trying to figure out how to sum two columns in a table with a mysql query.

something link this


mysql_query("SELECT *, SUM(price) AS price FROM banner_table BT, banner_views BV GROUP BY banner_key WHERE status != 'Expired' AND BT.banner_key = BV.id count(views) as views WHERE BT.banner_key = BV.id");


What Is supposed to happen

1. Sum the column banner_fees in the table banner_table

2.
3. count the number of times each banner_id in the table banner_views occurs where the value in the table 'banner_id' column status does not equal
expired.

I hope this makes sense :confused:

[kproc]

the code the sums the works but the code that counts the number of views for the found active banners does not work. The end goal is to count to views for all active banners. a banner is determined to be active based on the column status in the table banner_tables all banners ate active unless the column value is = expired

the below will echo to total for the count but does not sum the values

$totalEarning = $count * 0.02;
//Get total dollars for all active adds
$query_banner_views = ("SELECT banner_fees, banner_key, SUM(banner_fees) AS total FROM banner_table WHERE status != 'Expired' GROUP BY banner_key");

$banner_views = mysql_query($query_banner_views)or die("SQL Error: $query_banner_views<br>" . mysql_error());

while ($bv = mysql_fetch_assoc($banner_views)){
$active_banners = $bv['banner_key'];
$total_dollars = $bv['total'];
echo $total_dollars;

//count number of views for all active ads
$query_count_active_views = ("SELECT * , COUNT(banner_id) AS totalCount FROM banner_views WHERE banner_id = '$active_banners' GROUP BY banner_id");

$count_active_views = mysql_query($query_count_active_views)or die("SQL Error: $query_count_active_views<br>" . mysql_error());

$r = mysql_fetch_assoc($count_active_views);

echo $r['totalCount'];

}

[kproc]

its only looking at the first entry in table_banner. for some reason my while statement is not working, I think it has to do with the GROUP BY

[NightShift58]

Can you post the table structure?

[kproc]

I just emailed the two tables to you,

I'm confident the problem has to do with the group by, If I change the values each section of the code but only one piece at a time

[NightShift58]

Missing the main table...

[NightShift58]

The first SQL query should look like this:SELECT * FROM ads
WHERE member_id = '$user_id'
AND (status != 'Delete' OR paid != 'yes')
The OR needs to be bracketed within the AND.

[NightShift58]

You're using the variable "$r" twice within embedded loops to pull rows from tables. Not helpful to your loops...

[NightShift58]

This SQL query should look like this:$query_banner_views = "
SELECT banner_key, SUM(banner_fees) as total_fees
FROM banner_table
WHERE status != 'Expired'
GROUP BY banner_key
";And this variable assigned like this:$gross_dollars = $bv['total_fees'];