CODEWITHSUNDEEP
mysqlsql
Michelle
Michelle

Reputation: 2235

Query invoices that have more than one transaction associated with them

The table below contains payment transactions with each having an invoiceID. I'm trying write a query to list the invoiceID's that have more than one transaction associated with them.

    mysql> describe tblaccounts;
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(10)       | NO   | PRI | NULL    | auto_increment |
| userid      | int(10)       | NO   | MUL | NULL    |                |
| date        | datetime      | YES  | MUL | NULL    |                |
| description | text          | NO   |     | NULL    |                |
| amount      | decimal(10,2) | NO   |     | 0.00    |                |
| transid     | text          | NO   | MUL | NULL    |                |
| invoiceid   | int(10)       | NO   | MUL | 0       |                |
+-------------+---------------+------+-----+---------+----------------+

From what I've read I should be using a combination of 'GROUP BY' and 'HAVING' but I don't understand how to use them.

Upvotes: 1

Views: 144

Answers (2)

Gavin
Gavin

Reputation: 6460

SELECT invoiceid FROM tblaccounts GROUP BY invoiceid HAVING COUNT(*) > 1

Upvotes: 0

Ozan Deniz
Ozan Deniz

Reputation: 1087

SELECT invoiceid
FROM tblaccounts
GROUP BY invoiceid HAVING COUNT(*)>1

You can use sth like this. First group by invoice id. Now we have groups that have same invoiceid. Then we select the groups whose count more than one with HAVING keyword. For more information check here: http://www.w3schools.com/sql/sql_having.asp and here: http://www.w3schools.com/sql/sql_groupby.asp

Upvotes: 1

Related Questions