MySQL Query with Group By

Question

I have played around with many different queries, but haven't been able to locate the right syntax for this project.. I really hope someone can make sense of my mess.

I have a table with orders, one row per order, and each row containing info such as the customer number, PO#, and date of the order. A PO number can be tied to one or more orders, which is where I am getting lost. I am trying to create a query that will produce a single row containing the customer number, PO#, date of the earliest order, and date of the last order, grouped by the PO#.

Any pointers in the right direction will by much appreciated!

Which PO# do you want? That of the earliest order? That of the last order? Something else? — Matt Fenwick
– Matt Fenwick, Commented Feb 8, 2012 at 19:20
Do all rows with same PO have the same CustomerNumber too? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Feb 8, 2012 at 19:21

DRapp · Accepted Answer · 2012-02-08 19:24:09Z

2

This will get the order and range stuff...

select
      o.PONumber,
      o.CustomerID,
      count(*) as TotalOrders,
      min( o.OrderID ) as FirstOrder,
      min( o.OrderDate) as FirstOrderDate,
      max( o.OrderID ) as LastOrder,
      max( o.OrderDate) as LastOrderDate
   from
      Orders o
   group by
      o.PONumber,
      o.CustomerID

answered Feb 8, 2012 at 19:24

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ralfe · Accepted Answer · 2012-02-08 19:24:46Z

0

try this:

SELECT
    o1.`customer_number` as 'customer',
    o1.`po` as 'po_number',
    (
        SELECT
            MIN(`date`)
        FROM
            `orders`
        WHERE
            `id` = o1.`id`
    ) as 'earliest_date'
FROM
    `orders` o1
GROUP BY
    o1.`po`

Hope that helps.

answered Feb 8, 2012 at 19:24

ralfe

1,4542 gold badges15 silver badges27 bronze badges

1 Comment

DRapp Over a year ago

by doing a sub-select as a column will be a big performance hit, especially on larger tables. It requires each SELECT MIN() to be called for every ID vs using the standard MIN() / MAX() across all the qualified records. You have the group by which results the same one record per P/O

Jester · Accepted Answer · 2012-02-08 19:39:57Z

SELECT o.custumer, o.po_number, o.order_count, max.max_date, min.min_date
FROM
(
SELECT o.'costumer_number' as 'custumer', o.'po' as 'po_number', count('order_number') as 'order_count'
FROM 'orders' o
GROUP BY o.'costumer_number', o.'po'
) o
INNER JOIN (
    SELECT o.'costumer_number' as 'custumer', o.'po' as 'po_number', MAX('order_date') as 'max_date'
    FROM 'orders' o
    GROUP BY o.'costumer_number', o.'po'    
) max ON o.'customrer' = max.'costumer' and o.'po_number' = max.'po_number'
INNER JOIN (
    SELECT o.'costumer_number' as 'custumer', o.'po' as 'po_number', min('order_date') as 'min_date'
    FROM 'orders' o
    GROUP BY o.'costumer_number', o.'po'    
) min ON o.'customrer' = min.'costumer' and o.'po_number' = min.'po_number'
ORDER BY 1,2

That shold clear it out

Collectives™ on Stack Overflow

MySQL Query with Group By

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related