CODEWITHSUNDEEP
mysql
Rossa Urs
Rossa Urs

Reputation: 17

Does order of records in table matter?

I'm working on a simple stock market portfolio manager and have a question which, I believe, relates to the order of the records in my database table.

Here's my table:

CREATE TABLE 'transactions' (
  'positionID' int(11) DEFAULT NULL,
  'date' date DEFAULT NULL,
  'mode' varchar(100) DEFAULT NULL,
  'company' varchar(50) DEFAULT NULL,
  'units' int(11) DEFAULT NULL,
  'price' float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Just to summarize, my table of transactions contains a record for my initial purchase, and subsequent dividend reinvestments.

Here's my data:


15,2018-02-28,Reinvestment,Ford,877,13.25
15,2017-11-30,Reinvestment,Ford,26,10.47
15,2016-09-13,Purchase,Ford,20,13.07
15,2019-08-30,Reinvestment,Ford,25,12.64

44,2015-12-05,Purchase,Oracle,87,52.18
44,2017-07-13,Reinvestment,Oracle,449,60.64
44,2018-03-29,Reinvestment,Oracle,84,54.01
44,2019-05-07,Reinvestment,Oracle,59,67.24

21,2016-10-13,Purchase,Ford,10,6.54
21,2018-03-30,Reinvestment,Ford,439,6.63
21,2017-12-30,Reinvestment,Ford,13,5.24
21,2019-09-29,Reinvestment,Ford,13,6.32

I would like to list all my positions grouped by positionid and accumulated units. With each positionid, I would like to list initial purchase date (mode = 'Purchase') and duration of holding since initial purchase. This is what I wrote:

SELECT
  t.positionid,
  t.company,
  IF(t.mode = 'Purchase', t.date, '') AS purchaseDate,
  IF(t.mode = 'Purchase', DATEDIFF(CURDATE(), t.date), '') AS duration,
  ROUND(SUM(t.units), 3) AS currentHolding,
  IF(t.mode = 'Purchase', ROUND(t.price, 3), '') AS cost
FROM transactions t
GROUP BY t.positionid
ORDER BY t.company, t.date

Here's the result I got:

21,Ford,2016-10-13,1117,475.000,6.540
15,Ford,,,948.000,
44,Oracle,2015-12-05,1430,679.000,52.180

My problem is ... for position 15, I don't see the purchaseDate and duration. I believe this is because the records for position=15 are not in chronological order. If the 'Purchase' record were the first, I could have gotten what I wanted.

How do I fix this?

Upvotes: 0

Views: 50

Answers (2)

forpas
forpas

Reputation: 164174

The problem is that you use GROUP BY without aggregating the columns in the SELECT list and you don't use in the GROUP BY clause the column t.company which you select.
This may be allowed by MySql but sometimes leads to unexpected results.
Change to this:

SELECT
  t.positionid,
  t.company,
  MAX(IF(t.mode = 'Purchase', t.date, '')) AS purchaseDate,
  MAX(IF(t.mode = 'Purchase', DATEDIFF(CURDATE(), t.date), '')) AS duration,
  ROUND(SUM(t.units), 3) AS currentHolding,
  MAX(IF(t.mode = 'Purchase', ROUND(t.price, 3), '')) AS cost
FROM transactions t
GROUP BY t.positionid, t.company
ORDER BY t.company, purchaseDate

See the demo.
Results:

| positionid | company | purchaseDate        | duration | currentHolding | cost   |
| ---------- | ------- | ------------------- | -------- | -------------- | ------ |
| 15         | Ford    | 2016-09-13 00:00:00 | 1147     | 948            | 13.070 |
| 21         | Ford    | 2016-10-13 00:00:00 | 1117     | 475            | 6.540  |
| 44         | Oracle  | 2015-12-05 00:00:00 | 1430     | 679            | 52.180 |

Upvotes: 1

Payam Mohammadi
Payam Mohammadi

Reputation: 136

One way to do this is by using a simple join

SELECT
  t.positionid,
  t.company,
  pt.date AS purchaseDate,
  DATEDIFF(CURDATE(), pt.date) AS duration,
  ROUND(SUM(t.units), 3) AS currentHolding,
  ROUND(pt.price, 3) AS cost
FROM transactions t
LEFT JOIN transactions pt ON t.positionid = pt.positionid
WHERE pt.mode = 'Purchase'
GROUP BY t.positionid
ORDER BY t.company, t.date

Upvotes: 0

Related Questions