SQL to select distinct with Group By

[ljwilson]

I am needing to only pick one of the records if two records have the same transactionnum. I have tried using DISTINCT, but am getting a SQL error. What do I need to do?

mysql> select paymentid, transactionnum, paymentmethod, tenderedamount, paymentamount , paymentdue from tblPayments where reportid = 885 and paymentmethod = 'Cash' order by paymentid asc;
+-----------+----------------+---------------+----------------+---------------+------------+
| paymentid | transactionnum | paymentmethod | tenderedamount | paymentamount | paymentdue |
+-----------+----------------+---------------+----------------+---------------+------------+
|     98472 |          98209 | Cash          |          20.00 |         20.00 |      14.93 | 
|     98476 |          98212 | Cash          |         155.00 |        155.00 |     152.66 | 
|     98477 |          98213 | Cash          |           0.00 |          0.00 |       0.00 | 
|     98480 |          98215 | Cash          |          10.00 |         10.00 |       9.13 | 
|     98481 |          98215 | Cash          |           0.00 |          9.13 |       9.13 | 
|     98486 |          98221 | Cash          |           7.00 |          7.00 |       6.58 | 
|     98496 |          98230 | Cash          |           0.00 |          0.00 |       0.00 | 
|     98505 |          98239 | Cash          |          20.00 |         20.00 |      18.05 | 
|     98519 |          98252 | Cash          |         100.00 |        100.00 |      21.62 | 
|     98520 |          98252 | Cash          |           0.00 |         21.62 |      21.62 | 
|     98521 |          98253 | Cash          |           0.00 |          0.00 |       0.00 | 
|     98532 |          98265 | Cash          |           0.00 |          0.00 |       0.00 | 
|     98536 |          98269 | Cash          |           0.00 |          0.00 |       0.00 | 
|     98540 |          98273 | Cash          |          10.00 |         10.00 |      10.00 | 
|     98545 |          98278 | Cash          |          40.00 |         40.00 |      30.00 | 
|     98553 |          98286 | Cash          |          10.00 |         10.00 |      10.00 | 
|     98556 |          98289 | Cash          |           0.00 |          0.00 |       0.00 | 
|     98563 |          98296 | Cash          |           5.00 |          5.00 |       5.00 | 
|     98572 |          98306 | Cash          |           0.00 |          0.00 |       0.00 | 
|     98573 |          98307 | Cash          |          10.00 |         10.00 |      10.00 | 
|     98595 |          98328 | Cash          |          20.92 |         20.92 |      10.92 | 
|     98597 |          98330 | Cash          |           1.00 |          1.00 |       0.38 | 
|     98600 |          98333 | Cash          |           5.00 |          5.00 |       5.00 | 
|     98601 |          98334 | Cash          |           1.25 |          1.25 |       1.19 | 
|     98614 |          98347 | Cash          |           9.00 |          9.00 |       8.97 | 
|     98629 |          98360 | Cash          |          20.00 |         20.00 |      15.53 | 
|     98631 |          98362 | Cash          |           0.00 |          0.00 |       0.00 | 
|     98632 |          98363 | Cash          |           2.35 |          2.35 |       2.32 | 
|     98633 |          98364 | Cash          |           5.50 |          5.50 |       2.50 | 
|     98647 |          98377 | Cash          |           0.00 |          0.00 |       0.00 | 
|     98650 |          98380 | Cash          |           0.00 |          0.00 |       0.00 | 
|     98655 |          98385 | Cash          |           0.00 |          0.00 |       0.00 | 
|     98676 |          98405 | Cash          |          11.00 |         11.00 |      10.97 | 
|     98678 |          98406 | Cash          |           9.00 |          9.00 |       9.00 | 
|     98683 |          98411 | Cash          |           5.00 |          5.00 |       5.00 | 
|     98686 |          98414 | Cash          |           0.00 |          0.00 |       0.00 | 
|     98697 |          98425 | Cash          |          20.00 |         20.00 |      19.00 | 
|     98698 |          98426 | Cash          |          20.00 |         20.00 |       2.40 | 
|     98703 |          98431 | Cash          |          20.00 |         20.00 |      20.00 | 
|     98704 |          98432 | Cash          |          20.00 |         20.00 |       6.66 | 
|     98705 |          98432 | Cash          |           0.00 |          6.66 |       6.66 | 
|     98727 |          98454 | Cash          |          20.00 |         20.00 |      10.00 | 
|     98750 |          98477 | Cash          |          22.00 |         22.00 |       7.00 | 
|     98751 |          98477 | Cash          |           0.00 |          7.00 |       7.00 | 
|     98754 |          98480 | Cash          |           0.00 |          0.00 |       0.00 | 
+-----------+----------------+---------------+----------------+---------------+------------+
45 rows in set (0.09 sec)

For example transactionnum 98215 has two records returned and I only want one of them (the one with tenderedamount greater than zero - although the field I am looking at is paymentdue so it really doesn't matter which one I get.

I have tried this:

mysql> select paymentmethod as METHOD, SUM(paymentdue) AS TOTAL, SUM(tenderedamount) AS TENDERED, COUNT(paymentid) AS COUNT, DISTINCT(transactionnum) FROM tblPayments WHERE reportid=885 GROUP BY paymentmethod;

But get this:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(transactionnum) FROM tblPayments WHERE reportid=885 GROUP BY paymentmet' at line 1

Any ideas?

Thanks!

 

[r937]

SELECT paymentid
     , transactionnum
     , paymentmethod
     , tenderedamount
     , paymentamount 
     , paymentdue 
  FROM tblPayments AS t
 WHERE reportid = 885 
   AND paymentmethod = 'Cash' 
   AND tenderedamount =
       ( SELECT MAX(tenderedamount)
           FROM tblPayments
          WHERE reportid       = t.reportid
            AND paymentmethod  = t.paymentmethod
            AND transactionnum = t.transactionnum )
ORDER 
    BY paymentid

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon