Solved

How can I change this select statement?

Posted on 2011-09-22
5
222 Views
Last Modified: 2012-05-12
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
Comment
Question by:brucegust
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 13

Expert Comment

by:themrrobert
ID: 36581705
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
 
LVL 7

Expert Comment

by:amigura
ID: 36581811
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
 
LVL 13

Expert Comment

by:haloexpertsexchange
ID: 36581869
you could always add a where income > 0 to the end of the query.
0
 

Author Comment

by:brucegust
ID: 36581910
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
 
LVL 7

Accepted Solution

by:
amigura earned 500 total points
ID: 36581992
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

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Ajax and PHP 9 53
Showing multiple maps using PHP and Google Maps 12 38
Returning errors to display in a dialog box - Ajax, Json, Php 16 49
php error 27 55
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question