MySQL SUM of one column, DISTINCT of ID column
mysql sum column values multiple rows
php mysql sum multiple columns
mysql sum with condition
mysql sum in where clause
mysql sum null as zero
mysql sum count
mysql sum distinct
I'm trying to create a summary report of our orders but having trouble extracting all my required data in a single query.
The data I'd like to extract:
- subtotal - SUM of all sale prices
- delivery total - SUM of all orders deliveryTotal
- orders - COUNT of DISTINCT orderIds
- quantity - SUM of all quantity ordered
Orders table (simplified for this example)
| orderId | deliveryTotal | total | |---------|---------------|-------| | 1 | 5 | 15 | | 2 | 5 | 15 | | 3 | 7.50 | 27.50 |
Order items table
| orderItemId | orderId | productId | salePrice | quantity | |-------------|---------|-----------|-----------|----------| | 1 | 1 | 1 | 10 | 1 | | 2 | 2 | 1 | 10 | 1 | | 3 | 3 | 1 | 10 | 1 | | 4 | 3 | 2 | 10 | 1 |
My current query for extracting this data is
SELECT SUM(i.salePrice * i.quantity) as subtotal, SUM(DISTINCT o.deliveryTotal) as deliveryTotal, COUNT(DISTINCT o.orderId) as orders, SUM(i.quantity) as quantity FROM orderItems i INNER JOIN orders o ON o.orderId = i.orderId
Which results in a correct subtotal, order count and quantity sum. But delivery total is returned as 12.50 when I'm after 17.50. If I do
SUM(o.deliveryTotal) it will return 25.
EDIT: Desired results
| subtotal | deliveryTotal | orders | quantity | |----------|---------------|--------|----------| | 40.00 | 17.50 | 3 | 4 |
Because of the join, the
SUM(DISTINCT deliveryTotal) aggregate is being applied to a rowset including the values
5, 5, 7.5, 7.5 (distinct
5 + 7.5 = 12.5).
The rows your
SUM() acted on become more apparent if you simply do
SELECT o.* FROM orderItems i INNER JOIN orders o ON o.orderId = i.orderId
Instead you are asking for the
SUM() of all the values in
deliveryTotal, irrespective of their position in the join with
orderItems. That means you need to apply the aggregate at a different level.
Since you are not intending to add a
GROUP BY later, the easiest way to do that is to use a subselect whose purpose is only to get the
SUM() across the whole table.
SELECT SUM(i.salePrice * i.quantity) as subtotal, -- deliveryTotal sum as a subselect (SELECT SUM(deliveryTotal) FROM orders) as deliveryTotal, COUNT(DISTINCT o.orderId) as orders, SUM(i.quantity) as quantity FROM orderItems i INNER JOIN orders o ON o.orderId = i.orderId
Subselects are usually discouraged but there won't be a significant performance penalty for the subselect, none different from the alternative methods of using a join for it. The calculation has to be done on a separate aggregate from the existing join no matter what. Other methods would place a subquery
CROSS JOIN in the
FROM clause, which performs the same thing we placed here in the subselect. Performance would be the same.
SUM(DISTINCT) Based on Other Columns, +------+-------+------------+------------+ | id | rate | first_name | last_name If you want to get all sum of those little " sums ", you will get a sum of all table. You can do this by making the values you are summing distinct. This is possible You have to use FORMAT to avoid MySQL truncating the decimal places. COUNT() function and SELECT with DISTINCT on multiple columns. You can use the count() function in a select statement with distinct on multiple columns to count the distinct rows. Here is an example: SELECT COUNT(*) FROM ( SELECT DISTINCT agent_code, ord_amount,cust_code FROM orders WHERE agent_code='A002'); Output: COUNT(*) ----- 6 Pictorial
Here is a blog post that shows exactly what I was looking for. Maybe this can help others too.
The formula is something like this:
SUM(o.deliveryTotal) * COUNT(DISTINCT o.orderId) / COUNT(*)
sum() of column by distinct() id column, https://tiaashish.wordpress.com/2014/01/31/mysql-sum-for-distinct-rows-with-left-join/. Here is a blog post that describes exactly this situation I am also grouping by the Column that I am joining both of these tables on. For speeds sake I can't use subqueries, because this table already has about 15,000 rows. What I am trying to achieve is to sum distinct values of the amount column, but I want the rows to be made distinct by their primary key bva.VendorAdjustmentID not by their value.
Select per Order in the Inner Select and than sum it up
Select SUM(subtotal) as subtotal, sum(deliveryTotal) as deliveryTotal, count(1) as orders, sum(quantity) as quantity from ( SELECT SUM(i.salePrice * i.quantity) as subtotal, o.deliveryTotal as deliveryTotal, SUM(i.quantity) as quantity FROM orders o INNER JOIN orderItems i ON o.orderId = i.orderId group by o.orderId) as sub
How to get the sum for every distinct value in another column in , You can get the sum for every distinct value in another column with the help of mysql> create table SumOfEveryDistinct -> ( -> Id int not null, mysql> insert into SumOfEveryDistinct values(10,100); Query OK, 1 row affected threadid views ----- ----- 1 5 1 5 2 10 3 15 4 10. That looks like a database-design flaw to me ;) I am looking for a select where I want to find the SUM of views for distinct threadids, not distinct views.
The below query results exactly what you need
SELECT SUM(conctable.subtotal), SUM(conctable.deliveryTotal), SUM(conctable.orders), SUM(conctable.quantity) from (SELECT SUM(i.salePrice * i.quantity) as subtotal, o.deliveryTotal as deliveryTotal, COUNT(DISTINCT o.orderId) as orders, SUM(i.quantity) as quantity FROM orderItems i JOIN orders o ON o.orderId = i.orderId group by i.orderid) as conctable;
MySQL SUM - Calculating The Sum of Values, If you use the SUM() function in a SELECT statement that returns no row, the SUM() Third, use the SUM() function to calculate the total values in the n column: In this case, the SUM() with the DISTINCT option only calculates the sum of Try Common table expression instead of temp table with Row_number() function to get the distinct values and then apply sum on required column of the CTE. Here is sample code for you. ;WITH CTE AS
Using MySQL DISTINCT to Eliminate Duplicates, When querying data from a table, you may get duplicate rows. If a column has NULL values and you use the DISTINCT clause for that column, MySQL You can use the DISTINCT clause with an aggregate function e.g., SUM, AVG, and Types · CREATE TABLE · Primary Key · Foreign Key · UNIQUE Constraint · CHECK You can get the sum for every distinct value in another column with the help of aggregate function SUM () with GROUP BY command. To understand the above concept, let us create a table. The query to create a table is as follows: mysql> create table SumOfEveryDistinct -> (-> Id int not null, -> Amount int ->); Query OK, 0 rows affected (0.59 sec)
mysql, I have a somewhat complicated select query which returns, in part, the following resultset (as an example): threadid views -------- ----- 1 5 1 5 2 I have data like this: Unique ID Version 1234 1 1234 1 1234 2 1234 2 1234 3 1111 2 1111 2 2223 3 3556 2 3556 2 4432 1 I need to culculate the average version number for each unique ID - but I don't want to include duplicate versions from the same Unique ID in this calculation. So ideally, I want
COUNT (DISTINCT) and Other DISTINCT Aggregates, Also, a query that uses a single DISTINCT aggregate consumes fewer of distinct values in the primary_key column of the date_dimension table: SELECT date_key, COUNT (DISTINCT product_key), SUM(qty_in_stock) Help Topic ID: The result: Using multiple columns in the DISTINCT clause. You may also specify two or more columns as using the SELECT – DISTINCT clause. As such, our example table contains duplicate values for employees and their IDs, so it will be good learning to see how DISTINCT clause returns the records as using both these columns in the single query.
- Can you give SQL Fiddle?
- @Sadikhasan here it is sqlfiddle.com/#!9/02cb0/1
- Updated question with desired results
- Will this query always be applied across the whole table, or do you expect to be adding a
- Always the whole table (will have a where statement in to select between dates) but no grouping.
- (SQLfiddle isn't responding for me right now to post the verified result, but I loaded it in my test database and it does indeed work)
- This seems indeed like the answer OP was looking for
- Excellent and is the answer I am looking for.