How to get aggregate and column data from Derby database joining three tables

I need to present data from a Derby database in a JTable, but two of the columns are aggregate sums from two one-to-many related tables. Here are example schema:

SHIFTDATA:
    ID
    DATE
    SHIFT
    FOOD_COST
    OFFICE_SUPPLIES
    REP_MAINT
    NET_SALES
    SALES_TAX

OTHERPAIDOUTS:
    ID
    SHIFTDATA_ID
    LABEL
    AMOUNT

DISCOUNTS
    ID
    SHIFTDATA_ID
    DISCOUNT_NAME
    AMOUNT

There are 0 or more OTHERPAIDOUTS for a given SHIFTDATA

There are 0 or more DISCOUNTS for a given SHIFTDATA

I need the equivalent of this statement, though I know I can’t combine aggregate expressions with "non-aggregate expressions" in a SELECT statement:

SELECT (S.FOOD_COST + S.OFFICE_SUPPLIES + S.REP_MAINT + SUM(O.AMOUNT)) AS TOTAL_PAIDOUTS, 
SUM(D.AMOUNT) AS TOTAL_DISCOUNT, 
S.NET_SALES,
S.SALES_TAX
FROM SHIFTDATA S, OTHERPAIDOUTS O, DISCOUNTS D WHERE O.SHIFTDATA_ID=S.ID AND D.SHIFTDATA_ID=S.ID

I see in other threads where adding the GROUP BY clause fixes these situations, but I guess adding in the second aggregate from a third table is throwing me off. I tried GROUP BY S.NET_SALES, S.SALES_TAX, and adding AND S.ID = 278 to the WHERE clause to get a known result, and the TOTAL_PAIDOUTS is correct (there are 3 related records in OTHERPAIDOUTS), but the returned TOTAL_DISCOUNTS is 3 times what it should be.

Needless to say, I’m not a SQL programmer! Hopefully you get the gist of what I’m after. I tried nested SELECT statements but just made a mess of it. This application is still in development, including the database structure, so if a different DB structure would simplify things, that may be an option. Or, if there's another way to programmatically build the table model, I'm open to that as well. Thanks in advance!!

======== Edit =============

In order to check the values from a known record, I'm querying with a specific SHIFTDATA.ID. Following is the sample table records:

SHIFTDATA:
ID  |FOOD_COST |OFFICE_SU&|REP_MAINT |NET_SALES |SALES_TAX
------------------------------------------------------
278 |0.00      |5.00      |10.00     |3898.78   |319.79

OTHERPAIDOUTS:
ID         |SHIFTDATA_&|LABEL                                   |AMOUNT
---------------------------------------------------------------------------
37         |278        |FOOD COST FUEL                          |52.00
38         |278        |MAINT FUEL                              |5.00
39         |278        |EMPLOYEE SHOES                          |21.48

DISCOUNTS:
ID         |ITEM_NAME                               |SHIFTDATA_&|AMOUNT
---------------------------------------------------------------------------
219        |Misc Discounts                          |278        |15.91

What I expect to see for this SHIFTDATA row in the JTable:

TOTAL_PAIDOUTS | TOTAL_DISCOUNT |NET_SALES |SALES_TAX
------------------------------------------------------
93.48          |15.91           |3898.78   |319.79

The best I can get is by adding the GROUP BY clause, but grouping by the fields from SHIFTDATA I get:

TOTAL_PAIDOUTS | TOTAL_DISCOUNT |NET_SALES |SALES_TAX
------------------------------------------------------
93.48          |47.73           |3898.78   |319.79

Solution

Here is the SQL query with required result.

Here are the table definitions, data, sql and the results:

CREATE TABLE shiftdata (
    id int,
    foodcost int,
    officesuppl int,
    repmaint int,
    netsales int,
    salestax int);

CREATE TABLE otherpaidouts (
    id int,
    shiftid int,
    label varchar(20),
    amount int);

CREATE TABLE discounts (
    id int,
    shiftid int,
    itemname varchar(20),
    amount int);

Create data for two shifts: 278 and 333. Both shifts have discounts, but only 278 shift has the otherpaidouts.

insert into shiftdata values (278, 0, 5, 10, 3898, 319);
insert into shiftdata values (333, 22, 15, 100, 2111, 88);
insert into otherpaidouts values (37, 278, 'Food Cost FUEL', 52);
insert into otherpaidouts values (38, 278, 'Maint FUEL', 5);
insert into otherpaidouts values (39, 278, 'Empl SHOES', 21);
insert into discounts values (219, 278, 'Misc DISCOUNTS', 15);
insert into discounts values (312, 333, 'Misc DISCOUNTS', 25);

The Query:

SELECT sd.id, sd.netsales, sd.salestax,
  IFNULL(
    (SELECT SUM(d.amount) FROM discounts d WHERE d.shiftid=sd.id), 0) AS total_discount,
  (SELECT sd.foodcost + sd.officesuppl + sd.repmaint + IFNULL(SUM(op.amount), 0) FROM otherpaidouts op WHERE op.shiftid=sd.id) AS total_paidouts
FROM shiftdata sd;

The Result:

+------+----------+----------+----------------+----------------+
| id   | netsales | salestax | total_discount | total_paidouts |
+------+----------+----------+----------------+----------------+
|  278 |     3898 |      319 |             15 |             93 |
|  333 |     2111 |       88 |             25 |            137 |
+------+----------+----------+----------------+----------------+