Please help me construct this query

[thepreacher]

Please help me construct this query

select * from payment_transaction where username = "$username" and substring(transactionId, 4, 8) as startdate is between "$startdate" and "$endfate" order by startdate sum paymenamt as subtotal for each date;

Please do not read the above as a well constructed query its not. Its just what i hope the query to achieve. Basically the query results will be used to generate a report which will list the transactions generated by a staff within a date range. The report will have to have a sub total for each date then a grand total.

[VGR]

for the grand total, you'll have to use a second query, or a front-end script, or a reporting tool (report generator : it'll do the second SQL call anyway, but it'll be nicer ;-)

select substring(transactionid,4,8) AS startdate,sum(payment) AS subtotal from payment_transaction where username='$username' and startdate>='$startdate' and startdate<='$enddate' GROUP BY substring(transactionid,4,8) order by startdate ASC;

notes :
- you use string arithmetic on dates
- you should add a column for substring(transactionid,4,8)
- it's ugly because you can't use an alias in the WHERE clause (but you could in an HAVING clause)
- if you get an error for the ORDER BY clause, replace startdate with substring(transactionid,4,8) one more time...