Retrieving currency field data

 
0
 

All right. So i have child, parent, activity and register table and i want know how much money a parent will pay for his child if the child have 1 or more activities.
So i came out with this code:

SELECT
  CONCAT_WS (' ', parent_title, parent_fname, parent_sname) AS 'Parent/Carer Name',
  CONCAT('£', act_price) AS Amount,
  COUNT(*) AS 'Nr of Act.'
FROM Activity A, Parent P, Child C, Register R
WHERE C.parent_id = P.parent_id AND
      C.child_id = R.child_id AND
      A.act_id = R.act_id
GROUP BY P.parent_id

and see atachment for result.

the thing is in the Amount column the value are all £0.00
any ideea anyone?

Attachments a.html (1.68 KB)
<html>
<head>
<title>Query SELECT   CONCAT_WS (' ', parent_title, parent_fname, parent_sname) AS 'Parent/Carer Name',   CONCAT('', act_price) AS Amount,   COUNT(*) AS 'Nr of Act.' FROM Activity A, Parent P, Child C, Register R WHERE C.parent_id = P.parent_id AND       C.child_id = R.child_id AND       A.act_id = R.act_id GROUP BY P.parent_id, Fri Jan 06 14:24:55 2012
</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body><h1>Query SELECT   CONCAT_WS (' ', parent_title, parent_fname, parent_sname) AS 'Parent/Carer Name',   CONCAT('', act_price) AS Amount,   COUNT(*) AS 'Nr of Act.' FROM Activity A, Parent P, Child C, Register R WHERE C.parent_id = P.parent_id AND       C.child_id = R.child_id AND       A.act_id = R.act_id GROUP BY P.parent_id, Fri Jan 06 14:24:55 2012
</h1>
<table border=1 cellspacing=1 cellpadding=0><tr>
<th>Parent/Carer Name</th><th>Amount</th><th>Nr of Act.</th></tr>
<tr>
<td>Mrs Betty Horner</td><td>&pound;0.00</td><td>1</td></tr>
<tr>
<td>Ms Little Weed</td><td>&pound;0.00</td><td>2</td></tr>
<tr>
<td>Mrs Marge Simpson</td><td>&pound;0.00</td><td>3</td></tr>
<tr>
<td>Mrs Fred Drake</td><td>&pound;0.00</td><td>2</td></tr>
<tr>
<td>Mrs Branston Pickles</td><td>&pound;0.00</td><td>1</td></tr>
<tr>
<td>Mrs Liane Cartman</td><td>&pound;0.00</td><td>4</td></tr>
<tr>
<td>Mr Dougal Roundabout</td><td>&pound;0.00</td><td>2</td></tr>
<tr>
<td>Mrs Ethel Menace</td><td>&pound;0.00</td><td>3</td></tr>
<tr>
<td>Mrs Victor Minx</td><td>&pound;0.00</td><td>3</td></tr>
<tr>
<td>Ms Trixie Pott</td><td>&pound;0.00</td><td>1</td></tr>
<tr>
<td>Mrs John Little</td><td>&pound;0.00</td><td>3</td></tr>
</table>
</body></html>

Featured Replies in this Discussion

 
1
 

Replace

CONCAT('£', act_price) AS Amount,

by

sum(act_price) AS Amount,
 
0
 

still the same mate

 
0
 

found it

CONCAT('£', SUM(act_price)) AS Amount,

thank you anyway for your help mate

You
This article has been dead for over six months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: