CODEWITHSUNDEEP
mysqlsql
mrvllus
mrvllus

Reputation: 149

sum of customer transactions

I have a mysql database with a purchases table:

1   Deal Certificate    int(3)  
2   Purchase Price  decimal(4,2) 
3   Purchase Date   timestamp   
4   Serial Number   varchar(9) 
5   Name    varchar(23)     
6   Email   varchar(42)

After I run the following query:

 SELECT * 
   FROM `PURCHASES` 
  WHERE `Purchase Date` 
between '2012-01-01' and '2012-01-31'
  order by email

I see results:

345     36.00   2012-01-17 16:42:33     7757-3097   T T     [email protected]
327     6.00    2012-01-05 10:40:57     0223-3945   R T     [email protected]
329     12.00   2012-01-02 04:54:45     1087-7072   P B     [email protected]
294     7.00    2012-01-02 04:59:11     4144-2426   P B     [email protected]
285     6.00    2012-01-02 05:12:39     8027-1641   P B     [email protected]
1079    21.00   2012-01-02 05:05:07     2447-7971   P B     [email protected]
331     10.00   2012-01-02 19:14:38     3916-5434   y a     [email protected]

My question is, how would I write a query to total every's purchases for the month in 1 line so I get a result like the following?

345     36.00   2012-01-17 16:42:33     7757-3097   T T     [email protected]
327     6.00    2012-01-05 10:40:57     0223-3945   R T     [email protected]
329     46.00   2012-01-02 04:54:45     1087-7072   P B     [email protected]  
331     10.00   2012-01-02 19:14:38     3916-5434   y a     [email protected]

Notice all of user P B's tranactions are totaled on one row.

Thanks

Upvotes: 0

Views: 125

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

Actually, your example is not appropriate or you're missing information about the problem itself. Answer this question: If you want one line including a total what serial number do you want for that line? It is against common sense to have a total with detailed information (as long as you don't specify a criteria such as and also I want the most recent purchase date for each email).

Another way to see this is: What criteria did you apply to select this serial number 1087-7072 instead of 2447-7971for [email protected]? The same questions applies for fields 1 and 3.

So, what I understand it would be useful for you (and minimal, of course) would be this:

36.00   T T     [email protected]
6.00    R T     [email protected]
46.00   P B     [email protected]  
10.00   y a     [email protected]

You can get this with the following query (based on your table schea, I assume name has those values P B):

select sum(`Purchase Price`) as total_sum, name, email from purchases
where `Purchase Date` between '2012-01-01' and '2012-01-31'
group by email, name
order by email

Let me know if this is what you're (actually) looking for.

Upvotes: 0

juergen d
juergen d

Reputation: 204884

Unverified:

SELECT  `Deal Certificate`,
        sum(`Purchase Price`) as sum_price
        `Purchase Date`, 
        `Serial Number`,
        Name,
        Email
FROM PURCHASES 
WHERE Purchase Date between '2012-01-01' and '2012-01-31' 
group by `Deal Certificate`
order by email

Upvotes: 2

Related Questions