在别名中使用SUM

Question

I want to use SUM function in alias please look at following code i hope you understand it:

     SELECT PATIENTS.NAME,
       PATIENTS.FAMILY,
       COST.COST,
       COST.REALDATEYEAR,
       COST.REALDATEMONTH,
       COST.REALDATEDAY,
       CONTRACTS.ID,
       CONTRACTS.FRANSHIZ AS CO1,
       PATIENTS.COMPANY,
       CONTRACTS.COMPANY,
       COST.TYPE,
       TARRIFS.ID,
       TARRIFS.ACTION,
       TARRIFS.COST       AS CO
  FROM PATIENTS
 INNER JOIN COST
 INNER JOIN CONTRACTS
 INNER JOIN TARRIFS
    ON PATIENTS.SINGLE_ID = COST.SINGLE_ID
   AND COST.TYPE = TARRIFS.ID
   AND CONTRACTS.ID = PATIENTS.COMPANY

i want to use SUM for co1 and co,

Answer 1

You need to use GROUP BY when using SUM.

Your query something like this.

SELECT PATIENTS.NAME,
       PATIENTS.FAMILY,
       COST.COST,
       COST.REALDATEYEAR,
       COST.REALDATEMONTH,
       COST.REALDATEDAY,
       CONTRACTS.ID,
       SUM(CONTRACTS.FRANSHIZ) AS CO1,
       PATIENTS.COMPANY,
       CONTRACTS.COMPANY,
       COST.TYPE,
       TARRIFS.ID,
       TARRIFS.ACTION,
       TARRIFS.COST AS CO
  FROM PATIENTS
 INNER JOIN COST
 INNER JOIN CONTRACTS
 INNER JOIN TARRIFS
    ON PATIENTS.SINGLE_ID = COST.SINGLE_ID
   AND COST.TYPE = TARRIFS.ID
   AND CONTRACTS.ID = PATIENTS.COMPANY
 GROUP BY CONTRACTS.FRANSHIZ