SQL query for getting Count of distinct items along with Total [closed]

Question

I'm using MySql (hosted locally at xampp) to store sale information

I want the output something like this

Is there a single query that gets me the output in this format or should I write multiple queries to get the intended result?

I'm very new to SQL and I don't know how to implement it or what to look for in the internet.

Answer 1

You want something like this (works for >= 5.5 at least):

SELECT
  i_name,
  i_price,
  COUNT(i_qty) AS "Number sold",
  SUM(i_price) AS "Total revenue"
FROM item
GROUP BY i_name, i_price
ORDER BY i_price;

Result:

i_name  i_price  Number sold  Total revenue
Tea     10                 3             30
Coffee  10                 2             20
Idly    23                 1             23
Parota  25                 2             50
Dosa    35                 1             35
Meals   75                 1             75

Aggregated functions are covered in the documentation here.

There is another way of doing what you want - and one well worth investigating in more detail if you plan to be doing lots of SQL - only works for version 8.0:

SELECT DISTINCT
  i_name,
  i_price,
  COUNT(i_qty) OVER (PARTITION BY i_name ORDER BY i_name) AS "Number sold",
  SUM(i_price) OVER (PARTITION BY i_name ORDER BY i_name) AS "Total revenue"
FROM item
ORDER BY i_price;

Result: - the same!

See the fiddle here.

I would strongly urge you to become familiar with window functions like those used in the second piece of SQL - they are very powerful and well worth the effort to learn - they will repay that effort 10 times over...

Try deleting the DISTINCT and see what happens. You could also experiment with the AVERAGE(), MIN() and MAX() with various PARTITIONs - varying the ORDER BY clause can also be useful at times.

---

Take a look at this revised fiddle where I've included performance metrics. Now, it's very difficult to analyze performance where there are so few records and we don't know what's going on on the rest the dbfiddle server, but a couple of points to note:

• 1.) The plan for the window function solution is 9 lines long, whereas that for the simple grouping by is only 4. As a rule of thumb, the longer the plan, the slower the query!

• 2.) The timing for the window function query is consistently slower (4 runs) - not by much but with large datasets, this could vary. Check it out with your own system and data!

Answer 2

You need to be looking at GROUP BY and aggregate functions. See https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html for mysql's documentation.

In this case it looks like:

• You want to be grouping by Item Name, you can just use the field directly in SELECT then as it is part of the grouping.
• For Qty use the counting aggregate (COUNT(*)).
• For Total Amount use SUM(Price).
• Price could be a little more complicated. If the price for a given item is always the same (so something called Coffee is always 10) then either of the MIN() or MAX() aggregates will do, or you could include Price in the grouping (GROUP BY Item, Price) and not need any function. If the price of a named item can vary then you could use AVG(Price) to output the average, or you could chose to list each item+price combinations (so "2x coffee at 10, total 20, and 1x coffee at 12, total 12").