w3resource

SQL Exercises: Count how many salesmen each day register orders

SQL Aggregate Functions: Exercise-20 with Solution

From the following table, write a SQL query to count the number of orders based on the combination of each order date and salesperson. Return order date, salesperson id.

Sample table: orders


Sample Solution:

SELECT ord_date,salesman_id,COUNT(*) 
FROM orders 
GROUP BY ord_date,salesman_id;

Output of the Query:

ord_date	salesman_id	count
2012-07-27	5001		1
2012-08-17	5007		1
2012-04-25	5001		1
2012-09-10	5002		1
2012-10-05	5002		1
2012-10-10	5003		1
2012-09-10	5005		1
2012-08-17	5003		1
2012-06-27	5002		1
2012-09-10	5001		1
2012-10-05	5001		1
2012-10-10	5006		1

Code Explanation:

The said SQL query that count the number of orders placed on each date by each salesman. The "GROUP BY ord_date, salesman_id" clause groups the rows in the 'orders' table by the "ord_date" and "salesman_id" columns, and the "COUNT()" function counts the number of rows in each group.

Relational Algebra Expression:

Relational Algebra Expression: Count the number of salesmen registering orders for each day.

Relational Algebra Tree:

Relational Algebra Tree: Count the number of salesmen registering orders for each day.

Explanation:

 Syntax of counts the number of salesmen with their order date and ID registering orders for each day

Visual presentation:

Syntax of counts the number of salesmen with their order date and ID registering orders for each day

Practice Online


Query Visualization:

Duration:

Query visualization of Count the number of salesmen registering orders for each day - Duration

Rows:

Query visualization of Count the number of salesmen registering orders for each day - Rows

Cost:

Query visualization of Count the number of salesmen registering orders for each day - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Number of non NULL city values for salesmen.
Next SQL Exercise: Calculate the average price of all the products.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook