Display more sales order by staff first?

Databases
1 
SELECT * FROM sales WHERE submit_date >= 1277074800 AND submit_date <= 1277679600

It output like this:

3, Adam, Date_Submit
4, Paul, Date_Submit
5, Adam, Date_Submit
6, John, Date_Submit
7, Paul, Date_Submit
8, Adam, Date_Submit

I wanted to output which staff has the most sales come first, like this this:

3, Adam, Date_Submit
5, Adam, Date_Submit
8, Adam, Date_Submit
4, Paul, Date_Submit
7, Paul, Date_Submit
6, John, Date_Submit

As you can Adam has 3, Paul has 2, and John has as 1. How to display in this way?

2

You counted the sales each user made EVER, not only in the GIVEN PERIOD. That doesn’t mean it’s wrong, it all depends on what you want.

3

I did order by TotalUser… not user.

4

You order by the users that have the most sales ever, not the most sales in the given period.

And I’ll leave it to Rudy to tell you about the INNER JOIN :wink:

5

Thanks r937.

I have manged to this way:

SELECT * FROM sales f,
  (select username, count(*) as TotalUser from sales group by username) as f2  
  where f.username=f2.username  and 
  submit_date >= $StartMon AND submit_date <= $EndSun 
  order by TotalUser DESC";

Isn’t that better and shorter?

6 
SELECT sales.id
     , sales.staffname
     , sales.submit_date
  FROM ( SELECT staffname
              , COUNT(*) AS lines
           FROM sales
          WHERE submit_date >= 1277074800  
            AND submit_date <= 1277679600
         GROUP
             BY staffname ) AS totals
INNER
  JOIN sales
    ON sales.staffname = totals.staffname
 WHERE sales.submit_date >= 1277074800  
   AND sales.submit_date <= 1277679600
ORDER
    BY totals.lines DESC
     , sales.submit_date
7

There is no field for number of sales. To know the number of sales, the lines for each employee have to be counted.

8

Can you tell us the field names for number of sales and employee name.

9

at least tell us what is name of table field for emplyee?