0

I can't work out how to do this in MySQL:

I have tables transactions and clients.

I want to group the sales for each clients first month into ClientMonth 0, 1, 2 so I can look at how sales tend to change over time for all clients as a group (regardless of when they began dealing with us)

I've been trying to do this with

DATEDIFF(client_firstdeal_date.firstdeal_date, MAX(dealdate)+1) AS TenureDays, DATEDIFF(MAX(dealdate)+1, CURDATE()) AS FallowDays

and wanting to group by TenureDays but I'm not getting sensible results and I'm sure I'm misusing the mix of DATEDIFF, MAX and GROUP BY.

EDIT: Reading what I'd written and thinking more about what Adi said below, I went to the the start thinking, 'what exactly do I want (at each stage)'

Anyway, I solved my problem with the below (I really was making it too hard for myself):

SELECT
c.division, SUM(t.profitgbp), COUNT(ts.id), SUM(t.profitgbp)/COUNT(t.id) AS AverageProfPerTrans, ROUND(DATEDIFF(t.transdate,ft.firsttrans_date)/30,0) AS ClientMonth FROM transactions t LEFT OUTER JOIN client_firsttrans_date ft
ON t.accountno=ft.accountno JOIN clients c
ON c.accountno=t.accountno GROUP BY
ClientMonth, division

client_firsttrans_date is a view that I guess makes this easier to

I'm pleased with the results, as it outputs to a nice chart showing how transaction numbers drop of over the life of a client, with some divisions being more lossy than others.

Thanks to everyone who took the time to look at my question.

user2704416
  • 1
  • 2
  • "not getting sensible results" doesn't say much. What kind of results are you getting, and how do they differ from what you expected? – Adi Inbar Aug 21 '13 at 22:06
  • Thanks for your comment, Adi. I think it really shows that if you can't articulate the issue in natural language, you'll definitely struggle in any other language! – user2704416 Aug 22 '13 at 15:40
  • If you were able to answer your own question, you should post the solution as an answer and mark that answer accepted rather than adding it to the question. See this help page: http://meta.stackoverflow.com/help/self-answer. The page implies, though, that you need at least 15 rep to answer your own question. Do you have an "Answer Your Question" button at the bottom of the page? If you don't, please let me know. – Adi Inbar Aug 22 '13 at 16:29

0 Answers0