Question

Write SELECT statement that returns the "Representative ID" and their highest sales total of that sales representative, grouped by the " Representative ID" .

Representative ID    : rep_id column of rep_totals table

"Highest sales of Representative " : maximum sales_total of that representative.

Return only the rep_id whose highest sales_total is greater than 900000

Expected Output:

Representative ID Highest sales of representative

1 1274856.38

2 978465.99

3 1132744.56

Answer 1

Select rep_id AS “Representative ID Highest sales of representative” from rep_totals GROUP BY rep_id HAVING SUM(sales_total)>5000;

The above-mentioned command has to be written to fetch the expected output as given in the question.

GROUP BY CLAUSE:

• In this question, group by clause is used with the having clause.
• Basically group by clause is used to group a set of rows into a pack of summary rows by returning the value of that particular field/column.
• It is used to return one row specified for each group.
• In conclusion, group by clause decreases the number of rows in the output.
• Many aggregate functions like sum, Max, count, average and min can be used in groups by syntax but for further filtration of the group specified by GROUP BY clause HAVING Clause is used.

HAVING CLAUSE:

• It is basically used in select statements for further filtration of a group of rows done by Group by clause.
• It is mostly used after a group by clause based on a specified condition.
• Having Clause is similar to where clause but the only difference between having and where Clause is:
• WHERE filter the data and filtration is done on individual rows.
• Although GROUP BY clause filters the data and filtration is done on the group of rows as aggregated by Group by clause.
• Many aggregate functions like Sum, Max, min and count can be used in having clauses.

In this question firstly rep_id has to be mentioned as in the name of Representative ID Highest sales of representative.

Representative id has to be fetched from our present total sales table named as rep_totals.

• In this command firstly group by clause grouping the set of rows based on representative ID column.
• Then Having Clause is applied with sum function.
• Sum function has to be applied to add all the sales of a particular representative.
• Now, Having function applied on the set of rows to fetch the value of total sale (as already done by SUM function) done by a particular representative who is having their individual total sales greater than 900000.