Having trouble summing columns in SQL Server joined view
<p>Moi guys, Matt here. I'm having trouble with a relatively complicated view. I have a parts and service table that each have unique identifiers for a given part/service. I'm trying to link these to a service invoice table and subsequent view as a M:N relationship, so I've set up intermediary relational tables, with both the invoice number (invoice primary key) and part/service number (part/service primary key) as the combined primary key. Here's my code for the whole relationship and view:</p>

<pre><code>CREATE TABLE service_invoice
(   servinv_Num VARCHAR2(10) CONSTRAINT serv_snum_PK PRIMARY KEY, 
    servinv_EmpID NUMBER(6) CONSTRAINT serv_empnum_FK REFERENCES employee(empID), 
    servinv_CustID NUMBER(6) CONSTRAINT serv_custid_FK REFERENCES customer(custID),
    servinv_VIN VARCHAR2(25) CONSTRAINT serv_VIN_FK REFERENCES vehicle(vehicle_vin),
    servinv_Terms VARCHAR2(6) CONSTRAINT serv_trms_NN NOT NULL,
    servinv_Date DATE );


CREATE TABLE Parts
(   PartID VARCHAR2(10) CONSTRAINT Part_PartID_PK PRIMARY KEY,
    PartDesc VARCHAR2(50) CONSTRAINT Part_PartDesc_NN NOT NULL,
    PartCharge NUMBER(4,2) CONSTRAINT Part_PartCharge_NN NOT NULL );


CREATE TABLE Service
(   ServiceID VARCHAR2(10) CONSTRAINT Serv_ServID_PK PRIMARY KEY,
    ServDesc VARCHAR2(50) CONSTRAINT Serv_ServName_NN NOT NULL,
    ServCharge NUMBER(4,2) CONSTRAINT Serv_ServCharge_NN NOT NULL );


CREATE TABLE Serv_SI_Rel
(   SI_num VARCHAR2(10) CONSTRAINT ServSI_SInum_FK REFERENCES service_invoice(servinv_Num),
    ServiceID VARCHAR2(10) CONSTRAINT ServSI_ServID_FK REFERENCES Service(ServiceID),
    CONSTRAINT ServSI_SInum_ServID_PK PRIMARY KEY(SI_num, ServiceID) );

CREATE TABLE Parts_SI_Rel
(   SI_num VARCHAR2(10) CONSTRAINT PartSI_SInum_FK REFERENCES service_invoice(servinv_Num),
    PartID VARCHAR2(10) CONSTRAINT  PartSI_PartID_FK REFERENCES Parts(PartID),
    CONSTRAINT PartSI_SInum_PartID_PK PRIMARY KEY(SI_num, PartID) );


CREATE OR REPLACE VIEW ServiceInvoiceDoc
AS
( 
 SELECT si.servinv_Num, si.servinv_Date, si.servinv_Terms,
    es.empName,
    sc.custName, sc.custHouse, sc.custCity, 
        sc.custState, sc.custZIP, sc.custPhone, sc.custEmail,
    sv.vehicle_VIN, sv.vehicle_mileage,
    srel.ServiceID,
    prel.PartID,
    s.ServDesc, s.ServCharge,
    p.PartDesc, p.PartCharge,
    SUM(s.ServCharge) TotalServCharges,
    SUM(p.PartCharge) TotalPartsCharges,
    ( SUM(s.ServCharge)+SUM(p.PartCharge) ) SubTotalCharges,
    ( SUM(s.ServCharge)+SUM(p.PartCharge) )*0.0825 Taxes,
    ( SUM(s.ServCharge)+SUM(p.PartCharge) )*1.0825 TotalCharges

   FROM service_invoice si
JOIN employee es
    ON (es.empID = si.servinv_EmpID)
JOIN customer sc
    ON (sc.custID = si.servinv_CustID)
JOIN vehicle sv
    ON (sv.vehicle_VIN = si.servinv_VIN)
LEFT OUTER JOIN Serv_SI_Rel srel
    ON (srel.SI_Num = si.servinv_Num)
LEFT OUTER JOIN Parts_SI_Rel prel
    ON (prel.SI_num = si.servinv_Num) 
JOIN Parts p
    ON (prel.PartID = p.PartID)
JOIN Service s
    ON (srel.ServiceID = s.ServiceID) );
</code></pre>

<p>The error I get has to do with summing the individual parts and service charges in the M:N relationship. Here's the error code from the run:</p>

<pre><code>ORA-00937: not a single-group group function
</code></pre>

<p>I've tried fixing with a group by command, but the grouping identifier (service invoice) isn't included on the part or service tables, and the joins don't seem to link these up for a group. e.g. I tried calling GROUP BY si.servinv_Num</p>

<p>Can this be resolved at all or is it completely wrong? I have the option of dropping the M:N relationship as a 1:M and simply making a separate invoice for each part/service charge, but I would prefer to keep it compact and professional.</p>

<p>Any help would be greatly appreciated. Thank you so much for your time!</p>