CODEWITHSUNDEEP
mysqlcakephp
kSeudo
kSeudo

Reputation: 619

Find sum of records by user split by month in CakePHP

I want to retrieve from the database an array of values, one for each month, that is the sum of the users transaction prices.

For example: A user buy three items (1Euro, 2Euro, 3Euro) in March and two items in April(5euro, 10Euro).

I want to return an array containing something like {mar:6,apr:15}

Apologies if the explanation is poor but can someone point me in the right direction?

Thanks,

kSeudo.

Upvotes: 0

Views: 505

Answers (1)

TPete
TPete

Reputation: 2069

You have to group by purchaseDate and sum up the purchaseAmounts:

Select Date_Format(purchaseDate, '%M%Y') month, sum(purchaseAmounts)
From purchases
Group by Date_Format(purchaseDate, '%M%Y')

If you want the amounts per user and month, include the userID:

Select UserID, Date_Format(purchaseDate, '%M%Y') month, sum(purchaseAmounts)
From purchases
Group by UserID, Date_Format(purchaseDate, '%M%Y')

If you are looking for the purchases from a single user, add where UserID = That_Users_ID to the first statement.

Upvotes: 2

Related Questions