Contents
How to get monthly sales per product in SQL?
Let’s say we have a Product table (product_id, product_name) and a Sales table (product_id, date, qty, amount). What sql query would return the monthly sales per product, and include products with no sales?
How to calculate number of customers who have purchased a product?
If you want to know the dates as well, then include date (s.date) in the SELECT. Note that this assumes that any given product is purchased by a customer only once on each date. If a customer can have multiple records for a single product on one date, use COUNT (DISTINCT product_id) instead of COUNT (*).
How to get monthly sales for no product?
Clarification: I want a row for each possible month/product tuple. If there was no sales for a given month/product it should show zero qty/sales
How to get the total number of customers?
Note that this assumes that any given product is purchased by a customer only once on each date. If a customer can have multiple records for a single product on one date, use COUNT (DISTINCT product_id) instead of COUNT (*). To get the total number of customers, use a subquery:
How do you create a report in SQL?
The last thing we need to do in order to create a report is to combine reporting categories with the report data. We’ll simply use SQL queries created in “#2 SQL Example – Create reporting categories (Cartesian product)” and “#3 SQL Example – Get report data”. We’ll use both these SQL queries as a subquery for the main query.
How to query this SQL to get the best-selling product information?
This’s my database sample: orderdetails table with the ProID relate to products tabale How can I get the best-selling product information: ProID, ProPiecture, ProName, ProPrice from products table base on orderdetails table? Hopefully that gives you enough pointers to formulate the SQL yourself.
How many report categories do you need in SQL?
There are only 3 things worth mentioning here: Since we need all pairs in the final report, we’ll need to have 8 report categories (2 employees * 4 customers = 8 different pairs) Our tables are small and have only a few rows. Therefore, we could easily list all our employees and customers.
How to calculate month Wise sales in SQL?
Month wise sale Use Count to count month wise data. Thanks for contributing an answer to Stack Overflow! Please be sure to answer the question. Provide details and share your research! But avoid … Asking for help, clarification, or responding to other answers.
Why are there no sales in month 4?
Also, month 4 only has no sales in 2008, hence the 0, while it has sales in 2007 and still show up. Also, the report is actually for financial year – so I would love to have empty columns with 0 in both if there was no sales in say month 5 for either 2007 or 2008.