moona khan moona khan - 1 year ago 79
MySQL Question

select data based on three values from mysql

I have mysql table in this format

userid = int
amount = int
company =string
status =int
submission_time = date/time
approval_time =date/time

I want to sum amount of each userid, differentiated by userid , status , and submission_time{date} 30 days old

My idea for query.

SELECT sum(amount) as total,* FROM Tablename
WHERE status=0 and submission_time<=DATE_SUB(NOW(), INTERVAL 31 DAY)

I am not able pick data based on userid , status , and submission_time{date 30 days old.}

Kindly help me in building this query.

Answer Source

If you want 30 days old records only, try following query.

SELECT userid, SUM(amount) as total FROM Tablename
WHERE status=0 AND submission_time >= DATE_SUB(NOW(), INTERVAL 31 DAY)
GROUP BY userid

I'm still not sure you want to use COUNT() for amount column, as per my thinking you should use SUM()

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download