CODEWITHSUNDEEP
sqlaggregate-functions
Eric
Eric

Reputation: 245

SQL query syntax for count/sum

Suppose I had a table like follows:

ID   RANK     SALARY
---------------------
1    Manager  10
2    Temp     5
3    Manager  15
4    Manager  25

I want to find the count of all managers and sum of their salaries

SELECT COUNT(Rank), SUM(Salary)
  FROM Staff
 WHERE Rank = Manager

I wanted to know if my syntax was right.

Also for calculating max/min/avg salaries, would simply using the aggregate functions like min/max/avg work? (Having both min/max salaries in same table). I.e.

SELECT MAX(Salary), 
       MIN(Salary)
  FROM Staff

I was just wondering since it seems too simple.

Upvotes: 1

Views: 3481

Answers (3)

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

First of all, by the looks of your design, you might be missing quotes in your first query:

SELECT count(Rank), Sum (Salary)
FROM Staff
WHERE Rank = 'Manager'

Also, you might want to see all counts and sums for all Ranks (not only Managers), all at once. If so, you should try this:

SELECT Rank, count(Rank), Sum (Salary)
FROM Staff
GROUP BY Rank

Furthermore, you might want to use multiple aggregate functions in a Rank-by-Rank basis, which would look like this:

SELECT Rank, Max(Salary), min(Salary), AVG(Salary)
FROM Staff
GROUP BY Rank

And, yes, these are simple queries. In general cases SQL is simple, it is supposed to be.

Upvotes: 1

user825607
user825607

Reputation:

Looks good to me. Aggregate functions are very useful!

Upvotes: 0

Schroedingers Cat
Schroedingers Cat

Reputation: 3139

Broadly speaking, it is simple for simple requirements like this. So yes!

Upvotes: 0

Related Questions