MySQL grouping by two columns

Question

I have a table that lists charges against an order and also an ID of the invoice associated with the order.

orderID, financialType, invoiceID

I am looking to get all orders that have a financialType='17' but have different invoiceIDs

An example table:

orderID     financialType       invoiceID
123000      10                  NULL
123000      17                  900
123000      17                  901
123111      17                  902
123111      17                  902
123222      10                  NULL
123333      17                  900
123444      17                  900
123444      17                  901
123444      17                  902

In this case I am interested in 123000 (invoiced on 2 different invoices) and 123444 (invoiced 3 times).

My current query is:

SELECT orderID, count(*) as freq FROM orders WHERE financialType='17' group by orderID, invoiceID having freq > 1 order by freq desc

But this is giving me the total number of times each order was charged, not the number of times it was charged on different invoices. In my example I don't care about 123111 - this was charged twice but on the same invoice, this is fine.

I would hope to see:

orderID  freq
123000  2
123444  3

@user1698575 you could not use having in an alias. See my answer below. — Edper
– Edper, Commented Nov 29, 2013 at 12:08

T0to · Accepted Answer · 2013-11-29 12:06:25Z

1

This query works:

SELECT orderID, GROUP_CONCAT(invoiceID), count(DISTINCT invoiceID) as freq 
FROM orders 
WHERE financialType='17' 
group by orderID 
having freq > 1
order by freq desc

http://sqlfiddle.com/#!2/c2cc07/6

answered Nov 29, 2013 at 12:06

T0to

4222 silver badges5 bronze badges

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

Comments

Deepika Janiyani · Accepted Answer · 2013-11-29 12:06:20Z

SELECT 
count(orderID) as freq, orderID , invoiceID 
FROM orders  
WHERE financialType='17' 
group by orderID 
having freq > 1 
order by orderID desc

demo at http://sqlfiddle.com/#!2/794ff/5

use

SELECT 
count(orderID) as freq, orderID , GROUP_CONCAT(invoiceID) as invoiceIDs 
FROM orders 
WHERE financialType='17' 
group by orderID 
having freq > 1 
order by orderID desc

if you also want to see all the Invoice id's for an order

Demo at http://sqlfiddle.com/#!2/794ff/9

Edper · Accepted Answer · 2013-11-29 12:10:40Z

0

Try:

  SELECT orderID, count(*) as freq 
  FROM orders WHERE financialType='17'
  GROUP BY orderid
  HAVING count(*)>1

or if you want a unique invoice:

  SELECT orderID, count(DISTINCT invoiceID) as freq 
  FROM orders WHERE financialType='17'
  GROUP BY orderid
  HAVING count(*)>1

edited Nov 29, 2013 at 12:10

answered Nov 29, 2013 at 12:04

Edper

9,3621 gold badge32 silver badges49 bronze badges

Collectives™ on Stack Overflow

MySQL grouping by two columns

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related