CODEWITHSUNDEEP
mysqlsql
user721931
user721931

Reputation:

Why is my MySQL result incorrect when I use GROUP BY?

I have a simple table (ID, Member_ID, payment, date, payment_serial, running_total); I need the payment_serial to use the data for export to another system.

The export I need is all the payments made by a member (showing payment & date), sorted in order of the payment_serial and in a single row.

This was the first query I used:

SELECT
  `Member_ID`,
  IF(`payment_serial`=1,`payment`,0) AS `p1`,
  IF(`payment_serial`=1,`date`,0) AS `p1_date`,
  IF(`payment_serial`=1,`ID`,0) AS `p1_ID`,
  IF(`payment_serial`=2,`payment`,0) AS `p2`,
  IF(`payment_serial`=2,`date`,0) AS `p2_date`
  IF(`payment_serial`=2,`ID`,0) AS `p2_ID`,
FROM monthlies
WHERE `Member_ID` = 4198739

If I use this query, I see the payments I need, but each on a separate row (which creates big import problems at the next stage):

Member_ID    p1     p1_date  p1_ID    p2    p2_date  p2_ID ...
  4198739   34.50    41143    214      0      0       0    ... 
  4198739     0        0       0     34.50  41176    583   ...

I added GROUP BY:

SELECT
  IF(`payment_serial`=1,`payment`,0) AS `p1`,
  IF(`payment_serial`=1,`date`,0) AS `p1_date`,
  IF(`payment_serial`=1,`ID`,0) AS `p1_ID`,
  IF(`payment_serial`=2,`payment`,0) AS `p2`,
  IF(`payment_serial`=2,`date`,0) AS `p2_date`,
  IF(`payment_serial`=2,`ID`,0) AS `p2_ID`
FROM monthlies
WHERE `Member_ID` = 4198739
GROUP BY `Member_ID`

This gives me a single row for each member - but not each payment:

Member_ID    p1     p1_date  p1_ID    p2    p2_date  p2_ID ...
  4198739   34.50    41143    214      0      0       0    ...

Where am I going wrong?

Upvotes: 0

Views: 171

Answers (3)

Shubhanshu Mishra
Shubhanshu Mishra

Reputation: 6700

You are expecting the wrong output from GROUP_BY. In MySQL GROUP_BY is used in conjunction with commands like MAX, AVG, SUM etc to get all the results in a consolidated row.

So if you want to export the data the best way is to get all the rows without using group by as it will retain each row with its unique member id.

Upvotes: 1

Leandro Bardelli
Leandro Bardelli

Reputation: 11578

The GROUP BY is previous to the IF of the SELECT.

Upvotes: 0

podiluska
podiluska

Reputation: 51494

It's not entirely your fault. MySQL implements GROUP BY wrong. Any other SQL platform would give you a syntax error at this point.

Try

SELECT 
  MAX(IF(`payment_serial`=1,`payment`,0)) AS `p1`, 
  MAX(IF(`payment_serial`=1,`date`,0)) AS `p1_date`, 
  MAX(IF(`payment_serial`=1,`ID`,0)) AS `p1_ID`, 
  MAX(IF(`payment_serial`=2,`payment`,0)) AS `p2`, 
  MAX(IF(`payment_serial`=2,`date`,0)) AS `p2_date`, 
  MAX(IF(`payment_serial`=2,`ID`,0)) AS `p2_ID` 
FROM monthlies 
WHERE `Member_ID` = 4198739 
GROUP BY `Member_ID`

Upvotes: 1

Related Questions