0

Here is my sql query i just want my sellingPrice from sale table in descending order Need help Thanks in advance.

e.g.

SELECT salesMan.salesmanID, salesMan.name, SUM(sale.sellingPrice)
FROM salesMan 
INNER JOIN sale ON salesMan.salesmanID = sale.salesManID
GROUP BY salesmanID, name;
JG in SD
  • 5,427
  • 3
  • 34
  • 46
user3148422
  • 85
  • 1
  • 12
  • why dont you use `...SELECT * FROM .....` and then use a while loop.(CODING SIMPLIFIED) – Dev Man Apr 24 '14 at 22:24
  • 1
    @Mr.coder Because this is like 90% simpler, probably? – Tomas Pastircak Apr 24 '14 at 22:27
  • yeah so simpler code will reduce confusions – Dev Man Apr 24 '14 at 22:28
  • 2
    @Mr.coder Using `...SELECT * FROM...` is just wrong. You can read more about it here http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful –  Apr 24 '14 at 22:32
  • @Yoda i gave him that solution in the belief that he is using all the columns in his mysql table – Dev Man Apr 24 '14 at 22:35
  • 1
    @Mr.coder Even if you are using all columns in a table it is better to specify them. –  Apr 24 '14 at 22:36
  • 1
    @Mr.coder: seems like getting the resultset needed from the database would actually simplify the code **more** than writing code to "roll your own" `GROUP BY` and `SUM` operations. Seems like it would be more efficient too. With a smaller resultset for MySQL to prepare, fewer rows to fetch, and opportunity for the DBA to optimize for this query. – spencer7593 Apr 24 '14 at 22:56
  • @spencer7593 i didnt understand anything you said – Dev Man Apr 24 '14 at 22:59
  • @Mr.coder: Then maybe you shouldn't be advising people on database operations. But basically: let the database do the things it's good at, like grouping and summing data. It's silly to offload that to PHP. – cincodenada Apr 24 '14 at 23:02

3 Answers3

3

Then add:

order by SUM(sale.sellingPrice) desc
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Add the alias to the SUM column and use it to order your result set.

Select salesMan.salesmanID,salesMan.name, SUM(sale.sellingPrice) as sellingPriceSum
FROM salesMan INNER JOIN
     sale
     ON salesMan.salesmanID = sale.salesManID
GROUP BY salesmanID,name
ORDER BY sellingPriceSum desc;
Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
1

This should work:

Select salesMan.salesmanID, salesMan.name, SUM(sale.sellingPrice) AS totalSale
FROM salesMan INNER JOIN
     sale
     ON salesMan.salesmanID = sale.salesManID
GROUP BY salesmanID, name
ORDER BY totalSale DESC;

You can simplify the query like this:

SELECT sm.salesmanID, sm.name, SUM(s.sellingPrice) AS totalSale
FROM salesMan sm 
INNER JOIN sale s ON sm.salesmanID = s.salesManID
GROUP BY sm.salesmanID, sm.name
ORDER BY totalSale DESC;