Menu
I have a table OrderDetails with the following schema:
---------------------------------------------------------------- | OrderId | CopyCost | FullPrice | Price | PriceType | ---------------------------------------------------------------- | 16 | 50 | 100 | 50 | CopyCost | ---------------------------------------------------------------- | 16 | 50 | 100 | 100 | FullPrice | ---------------------------------------------------------------- | 16 | 50 | 100 | 50 | CopyCost | ---------------------------------------------------------------- | 16 | 50 | 100 | 50 | CopyCost | ---------------------------------------------------------------- I need a query that will surmise the above table into a new table with the following schema:
---------------------------------------------------------------- | OrderId | ItemCount | TotalCopyCost | TotalFullPrice | ---------------------------------------------------------------- | 16 | 4 | 150 | 100 | ---------------------------------------------------------------- Currently I am using a Group By on the Order.Id to the the item count. But I do not know how to conditionally surmise the CopyCost and FullPrice values.
Any help would be much appreciated.
Regards Freddie
905
A method to simplify this query and to run within a single select that I have used with success is to use a “conditional” sum in the SQL query. So rather than running COUNT(*) we will be running SUM(…) . We will combine the SUM() call with a CASE statement, so that it counts correctly.
SUM is used with a GROUP BY clause. The aggregate functions summarize the table data. Once the rows are divided into groups, the aggregate functions are applied in order to return just one value per group. It is better to identify each summary row by including the GROUP BY clause in the query resulst.
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. The original idea was to create the table in beginning of the query, so the (SELECT * FROM #TBL) could be used on the query itself, instead of defining the names on each GROUP BY.
Try
SELECT OrderId, COUNT(*) ItemCount, SUM(CASE WHEN PriceType = 'CopyCost' THEN Price ELSE 0 END) TotalCopyCost, SUM(CASE WHEN PriceType = 'FullPrice' THEN Price ELSE 0 END) TotalFullPrice FROM OrderDetails GROUP BY OrderId SQLFiddle
198
Try this query
select orderId, count(*) as cnt, sum(if(pricetype='CopyCost', CopyCost, 0)) as totalCopyCost, sum(if(pricetype='FullPrice', FullPrice, 0)) as totalFullPrice from tbl group by orderId | ORDERID | CNT | TOTALCOPYCOST | TOTALFULLPRICE | -------------------------------------------------- | 16 | 4 | 150 | 100 | If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
