CODEWITHSUNDEEP
mysqlsql
NGB
NGB

Reputation: 41

combine like information in a group by query

I think i'm missing something in my query for calculating the totals of invoices. the following code works to display all orders made between a certain range of order_id's and calculates correctly but I would like to have all order_id's show up in a single row instead of split up by individual item_id's as I don't want duplicate order_id's. 

select order_details.order_id, order_details.item_id, items.unit_price * 
order_details.order_qty as itemtotal
from order_details
join items on
order_details.item_id = items.item_id
where order_id >= 400 and order_id <= 700
group by order_details.order_id, order_details.item_id
order by order_id asc;

Query Result

I just need to figure out a way to group the order_id's together so I can calculate the total order on a single line instead of the totals for specific items purchased if that makes sense.

Upvotes: 1

Views: 35

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

If you need the total for order_id you should use an aggregation function as sum() and eventually group_concat() for item_id eg: 

  select
       order_details.order_id
     , group_concat(order_details.item_id) ids 
     , sum(items.unit_price * order_details.order_qty) as itemtotal
  from order_details
  join items on
  order_details.item_id = items.item_id
  where order_id >= 400 and order_id <= 700
  group by order_details.order_id
  order by order_id asc;

Upvotes: 1

Related Questions