SUM加倍结果的问题

Question

I've been working on this for two days, can't get it, I need some help.

Goal - Among other things...

• SUM all project timesheet entries
• SUM all project invoices

Group these by project (projectID) and display in a table.

On the same page as the table mentioned above there is a form which allows users to enter a new timesheet entry. On refresh, the runs and the table displays the updated timesheet total.

Current Situation - When I submit the timesheet form (example: 1.25 hours for project X), three things happen.

1. The form data is posted to the database. This works perfectly. The data input is exactly how it should be.
2. The page refreshes and the timesheet entry for project X updates 2.5 hours (it should have gone up by 1.25)
3. The invoice total also increments by the total invoice value for that project. i.e. if $5000 has been invoiced for project X, the addition of a new timesheet entry will push this to $10,000 the $15,000... and so on.

Query - As follows:

<?php
    $query = "SELECT tsm_projects.projectName AS projectName,   tsm_projects.projectID AS projectID, tsm_projects.value AS value, tsm_projects.estHours AS estHours, tsm_clients.clientName AS clientName, tsm_projects.estHours - SUM(tsm_timesheets.time) AS remaining, SUM(tsm_invoices.invoiceValue) AS invoiceValue, SUM(tsm_timesheets.time) AS totalTime FROM tsm_projects
    LEFT JOIN tsm_timesheets ON tsm_projects.projectID = tsm_timesheets.projectID
    LEFT JOIN tsm_clients ON tsm_clients.clientID = tsm_projects.clientID
    LEFT JOIN tsm_invoices ON tsm_invoices.projectID = tsm_projects.projectID
    WHERE projectType = 'active'
    GROUP BY tsm_timesheets.projectID 
    ORDER BY tsm_projects.projectName";
    $result = mysql_query($query) or die(mysql_error());
    while($row = mysql_fetch_array($result)){
    echo "<tr><td>". $row['projectName'] . " [" . $row['clientName'] . "]</td><td>$" . number_format($row[value], 2, '.', ',') . " [" . $row['estHours'] . "]</td><td>$" . $row['invoiceValue'] . "</td><td>" . number_format($row[totalTime], 2, '.', ',') ." [";
    if($row["remaining"] <= 0) {
    echo "<span class=\"redText\">" . $row['remaining'] . "</span>"; }
    else {
    echo "<span class=\"greenText\">+" . $row['remaining'] . "</span>"; }
    echo "]</td></tr>"; }
?>

SQL - I'm guessing the timesheet and/orinvoicing tables are likely relevant:

TABLE `tsm_timesheets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `projectID` varchar(10) NOT NULL,
  `activity` varchar(20) NOT NULL,
  `date` date NOT NULL,
  `time` decimal(4,2) NOT NULL,
  `timesheetID` varchar(10) NOT NULL,
  `memberID` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
)

TABLE `tsm_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `projectID` varchar(10) NOT NULL,
  `month` varchar(15) NOT NULL,
  `notes` varchar(255) NOT NULL,
  `invoiceValue` decimal(10,2) NOT NULL DEFAULT '0.00',
  `gstValue` decimal(10,2) NOT NULL DEFAULT '0.00',
  `fee` decimal(6,2) NOT NULL DEFAULT '0.00',
  `costs` decimal(6,2) NOT NULL DEFAULT '0.00',
  `invoiceNumber` varchar(15) NOT NULL,
  `dateSent` date NOT NULL,
  `dateDeposit` date NOT NULL,
  `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `addedBy` varchar(20) NOT NULL,
  `invoiceID` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
)

Hope someone can help. Thanks in advance.

rrfive

Answer 1

Aggregate function are calculated on a per result-row basis not per table-row.

You need to perform the grouping individually:

LEFT JOIN (
  SELECT projectID, SUM(invoiceValue) AS SumInvoiceValue
  FROM tsm_invoices
  GROUP BY projectID) i ON i.projectID = tsm_projects.projectID

The entire query:

SELECT p.projectName AS projectName, p.projectID AS projectID, p.value AS value,
    p.estHours AS estHours, c.clientName AS clientName,
    p.estHours - t.SumTime AS remaining,
    i.SumInvoiceValue AS invoiceValue,
    t.SumTime AS totalTime
FROM tsm_projects p
    LEFT JOIN tsm_clients c ON c.clientID = p.clientID
    LEFT JOIN (
        SELECT projectID, SUM(time) AS SumTime
        FROM tsm_timesheets
        GROUP BY projectID
      ) t ON p.projectID = t.projectID
    LEFT JOIN (
        SELECT projectID, SUM(invoiceValue) AS SumInvoiceValue
        FROM tsm_invoices
        GROUP BY projectID
      ) i ON i.projectID = p.projectID
WHERE projectType = 'active'
GROUP BY p.projectID 
ORDER BY p.projectName