Member Avatar for Octipus

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?

<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>
Edited by Octipus because: n/a
  • 2 Contributors
  • 3 Replies
  • 216 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Octipus
Member Avatar for smantscheff

Replace

CONCAT('£', act_price) AS Amount,

by

sum(act_price) AS Amount,
Member Avatar for Octipus

still the same mate

Member Avatar for Octipus

found it

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

thank you anyway for your help mate

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.