CODEWITHSUNDEEP
mysqlsqlgroup-bysumhaving-clause
MikeG
MikeG

Reputation: 107

MySQL select using row counts and grouping

I have 2 tables, orders and order_lineitems. orders contains the order status info (sold date, invoice no, type of sale, etc) order_lineitems contains the item(s) for each order in a one to many relationship. Since we provide shipping info by line item, ship_date is in the order_lineitems table, null if not shipped, a date if it is shipped.

I am trying to pull the orders where all items have shipped by comparing the number of line item rows against the line item rows that have a ship date. While I have successfully pulled all that info, I am unable to make the last step, limiting the result set to include only the completely shipped orders (number of rows = number of rows where ship_date is not null).

I know I am missing something simple, but just don't see it..

select sum(custom.lineitems) as totalitems, sum(custom.shipped) as totalshipped,
custom.invoice, z.shipregion
from (
 select a.invoice, count(a.invoice) as lineitems, 0 as shipped
 from order_lineitem a
 group by a.invoice

UNION ALL

 select b.invoice, 0 as notshipped, count(b.ship_date) as shipped
 from order_lineitem b
 where b.ship_date is not null
 group by b.invoice
) as custom

left join orders z on custom.invoice = z.invoice
where z.result = 0
and z.respmsg like 'Approved%'
and z.shipregion <> 'PENDING'
and z.cancelorder = 0
group by custom.invoice;

This returns a result set like so (one row for each invoice in the DB)

   totalitems   totalshipped    invoice shipregion
  4           2     1000    REGION08
  1           1     10001   REGION07
  1           1     10004   REGION05
  3           1     10006   REGION05
  2           2     10007   REGION04
  1           1     10008   REGION08    
  7           7     10009   REGION01    
  1           1     1001    REGION08

What I am looking for is a result set like this - only where totalitems = totalshipped

totalitems  totalshipped    invoice shipregion
  1           1     10001   REGION07
  1           1     10004   REGION05
  2           2     10007   REGION04
  1           1     10008   REGION08    
  7           7     10009   REGION01    
  1           1     1001    REGION08

Upvotes: 1

Views: 564

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Use HAVING clause

SELECT a.invoice, z.shipregion, COUNT(a.invoice) AS lineitems, 
       SUM(CASE WHEN a.ship_date IS NOT NULL THEN 1 ELSE 0 END) AS shipped
FROM order_lineitem a
LEFT JOIN orders z ON a.invoice = z.invoice AND z.result = 0 AND z.cancelorder = 0 AND 
                      z.respmsg LIKE 'Approved%' AND z.shipregion <> 'PENDING'                         
GROUP BY a.invoice HAVING lineitems = shipped

OR

SELECT a.invoice, a.shipregion, a.lineitems, a.shipped 
FROM (SELECT a.invoice, z.shipregion, COUNT(a.invoice) AS lineitems, 
             SUM(CASE WHEN a.ship_date IS NOT NULL THEN 1 ELSE 0 END) AS shipped
      FROM order_lineitem a
      LEFT JOIN orders z ON a.invoice = z.invoice AND z.result = 0 AND z.cancelorder = 0 AND 
                            z.respmsg LIKE 'Approved%' AND z.shipregion <> 'PENDING'                         
      GROUP BY a.invoice
    ) AS a WHERE a.lineitems = a.shipped

Upvotes: 1

Akshay
Akshay

Reputation: 1901

One more outer query needed.

select * from 
(
    \\Your whole query here
) as Result
where Result.totalitems  = Result.totalshipped

Upvotes: 0

Related Questions