Sort by Sales

Hi Experts,

I have the following MySQL table:

id |loc | agent | sales
1  |1    |  5         | 20.00
2  |1    |  7         | 80.00
3  |1    |  9         | 40.00
4  |2    |  5         | 200.00

Open in new window


I need to show on screen via php a detailed report, sorted by highest sales, first by location then by agent, so the rows will be sorted as 4, 2, 3, 1.

Any help would be greatly appreciated.
APD TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
select * from table order by sales

The difficulty deals with how you defined the sales column
show create table <tablename>
if sales is not a float, it is seen as a string in which case the order will be 2,3,4,1
You would need to convert/cast/change the sales column data from what it is now to a numeric.
PortletPaulEE Topic AdvisorCommented:
Its not clear what you want (to me)

ORDER By sales DESC

achieves the descending order of sales, if you want location first then:

ORDER BY location, sales DESC

if you want to use agent as a "tiebreaker" if the sales figure is the same then:

ORDER BY location, sales DESC, agent
APD TorontoSoftware DeveloperAuthor Commented:
Sorry, but example

id |loc | agent | sales
1  |1    |  5         | 150.00
2  |1    |  7         | 40.00
3  |1    |  9         | 80.00
4  |2    |  5         | 200.00

Open in new window


Here since location 1 ($270) > location 2 ($200), the resulting row order would be 1, 3, 2, 4
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

APD TorontoSoftware DeveloperAuthor Commented:
My first example was bad
arnoldCommented:
oh,
order by sales desc, location (asc or desc), agent (asc or desc)

 type of column still makes a difference.

Your example lacks the information on whether location and agent is of any significance to the order.
i.e. if line 5 was line four with location 3, would line 5 precede or follow line 4
i.e. do you want 5,4,2,3,1 or 4,5,2,3,1 similar to having another line that has the same location and same sales value, but agent is one off, would it follow or precede
line 6 same as 4 accept agent is number 6.
will you have 5,6,4,2,3,1 or 5,4,6,2,3,1 4,6,5,2,3,1 or 6,4,5,2,3,1

the variance deals with the location desc or asc and agent order desc or asc.
arnoldCommented:
are you looking at getting the per location totals?
if you do an aggregate (group by) with sum, you can not use agent in the criteria

select location,sum(sales) from <tablename> group by location order by sum(sales)

the agent has no impact nor use here.
PortletPaulEE Topic AdvisorCommented:
The result:
| id | loc | agent | sales |
|----|-----|-------|-------|
|  1 |   1 |     5 |   150 |
|  2 |   1 |     7 |    40 |
|  3 |   1 |     9 |    80 |
|  4 |   2 |     5 |   200 |
        

Open in new window

Produced by this query:
select
       t1.*
from Table1 t1
inner join (
  select loc, sum(sales) as sumsales from Table1 group by loc
  ) g1 on t1.loc = g1.loc
order by g1.sumsales DESC, Agent, Sales DESC
;

Open in new window


You need to sum the sales for each loc, then sort by that value.

see: http://sqlfiddle.com/#!9/f6ecef/1

details:
**MySQL 5.6 Schema Setup**:

    
    CREATE TABLE Table1
        (`id` int, `loc` int, `agent` int, `sales` int)
    ;
        
    INSERT INTO Table1
        (`id`, `loc`, `agent`, `sales`)
    VALUES
        (1, 1, 5, 150.00),
        (2, 1, 7, 40.00),
        (3, 1, 9, 80.00),
        (4, 2, 5, 200.00)
    ;
    
**Query 1**:

    select
           t1.*
    from Table1 t1
    inner join (
      select loc, sum(sales) as sumsales from Table1 group by loc
      ) g1 on t1.loc = g1.loc
    order by g1.sumsales DESC, Agent, Sales DESC
    

**[Results][2]**:
    | id | loc | agent | sales |
    |----|-----|-------|-------|
    |  1 |   1 |     5 |   150 |
    |  2 |   1 |     7 |    40 |
    |  3 |   1 |     9 |    80 |
    |  4 |   2 |     5 |   200 |

  [1]: http://sqlfiddle.com/#!9/f6ecef/1
  [2]: http://sqlfiddle.com/#!9/f6ecef/1/0

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
APD TorontoSoftware DeveloperAuthor Commented:
Paul,

That is what I wanted, except from the order by I removed agent. However, can you please explain what is g1?
arnoldCommented:
g1 is the table reference for the results to the (select query) preceding it.
APD TorontoSoftware DeveloperAuthor Commented:
Thank you!
PortletPaulEE Topic AdvisorCommented:
g1 is an "alias" for the subquery, just like t1 is an alias for the table

The FROM table_references clause indicates the table or tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see Section 13.2.8.2, “JOIN Syntax”. For each table specified, you can optionally specify an alias.

tbl_name [[AS] alias] [index_hint]
https://dev.mysql.com/doc/refman/5.0/en/select.html

I did not use AS but could have, the use of AS is optional in the syntax rules.


select
       t1.*
from Table1 AS t1
inner join (
  select loc, sum(sales) as sumsales from Table1 group by loc
  ) AS g1 on t1.loc = g1.loc
order by g1.sumsales DESC, Agent, Sales DESC
;

I did not use AS in those locations because the Oracle dbms does not like that syntax and I do more Oracle work than MySQL, sorry bad habit of mine.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.