Simple SQL question

Factual Questions
1

If you had a file of sales, and each row in the file had a salesperson’s code, a customer’s code, and a dollar value… and you wanted to display total the total dollar sales for each salesperson… starting with the one who generated the HIGHEST dollar figure, and ending with the lowest… how would you phrase the Select statement?

2

I think you would do something like this:


SELECT salesperson, sum(price) AS total FROM sales ORDER BY total DESC;

It’s the sum() function that I’m worried about. I remember seeing it before but I don’t know if it’s standard SQL.

The AS part is they key. It takes the result of sum(price), which is the sum of all the price datapoints associated with the sales person, and selects it as ‘total’. You can then use ORDER BY and DESC (descending) to get it in the right order.

3

freido, you need a GROUP BY clause there.

Try this:


SELECT salesperson,
       sum(price) AS total_sales
FROM   sales
GROUP BY salesperson
ORDER BY total_sales DESC

Without the GROUP BY clause, you’ll get each and every sale, starting with the one with the highest total, but it won’t be grouped by salesperson.

Zev Steinhardt

4

zev’s right. Do what he says.

5

With the database I use (DB2), it bitches if you don’t use the GROUP BY clause. Invalid SQL in its opinion.

I think you can also be lazy and not label the column with an AS clause. Then you refer to the column in the ORDER BY clause by number (in this case it would be 2).

6

Interesting. In my experience, MySQL does not support this feature and insists on using the AS clause.

7

firedo, that could be a database system feature though, not part of the SQL definition. SQL anywhere will let me add columns through a GUI, but generates correct SQL in the background without numeric references. A numeric reference is no (long term) use in SQL anyway since tables and views can be generated with columns in any order - the db being used probably does an “as 2” in the background for the user, but I don’t know DB2 & couldn’t swear to it.

8

In some versions of SQL, you can do this:


ORDER BY sum(price)

I don’t know if that is standard SQL.