Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

How can I change this select statement?

Here's my query:

$sour = "SELECT * FROM revenue_tally where student_id = '$student_id' AND client_type='Top 7 Prospect' GROUP by company_name";

When I include the "GROUP by company_name" dynamic, should I have more than one row with the same company name, the row that is being displayed isn't always the one that has income attached to it.

In other words, here's my table:

Gust's Seashells - $4240
Gust's Seashells - $0.00

The entry that's being displayed is the one with zero dollar amount. So it will show up on my page as:

Gust's Seashells - $0.00

I need to display the entry that has revenue attached to it. How can I write my query so when the grouping occurs, it displays the row with income as opposed to the row with no income?
0
brucegust
Asked:
brucegust
1 Solution
 
themrrobertCommented:
change to:

$sour = "SELECT * FROM revenue_tally where student_id = '$student_id' AND client_type='Top 7 Prospect' GROUP by company_name ORDER BY `VALUE_KEY` SORT DESC";

replace VALUE_KEY with the name of the income amount, the group by company name only groups like companies together, it has nothing to do with the dollar amount. Also, Mysql is usually smart enough to order by dollar amounts even if stored as text, so you should be good
0
 
amiguraCommented:
comp 1 200.00
comp 2 30.00
comp 2 20.00
comp 1 0.00
comp 3 10.00

comp 1 200
comp 2 50
comp 3 10

if thats what you want

$sour = "SELECT company, SUM(revenue or whatever) as revenue_total  FROM revenue_tally as revenue_total where student_id = '$student_id' AND client_type='Top 7 Prospect' GROUP by company_name";

Open in new window

0
 
haloexpertsexchangeCommented:
you could always add a where income > 0 to the end of the query.
0
 
brucegustPHP DeveloperAuthor Commented:
We're close!

Here's what I did:

$sour = "SELECT *, sum(actual_revenue) as revenue_total FROM revenue_tally where student_id = '$student_id' AND client_type='Top 7 Prospect' GROUP by company_name order by revenue_total DESC";

I'm still not there,however. Here's what I get as a result:


Louisville Oral Surgery  $17,600  
Evangel World Prayer Center  $10,000  
In Every Language  $0  
AVC Security Systems  $3,500

You'll notice that "In Every Language" shows up as 0$. That's because one of the "In Every Language" entries is $4240, while the other is $0. How do I get the $4240 value to show up?
0
 
amiguraCommented:
but does "$4240 In Every Language" fall into the category of student_id = '$student_id' AND client_type='Top 7 Prospect' and also not doing so

sum(actual_revenue) as revenue_total

is

$revenue_total=$row["revenue_totalt"];
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now