The thing is that I want to make a query to calculate how much it costs the realization of a product in my c # program, but in the mysql query I want to add the totals of the expenses for products and use the query:
use bd_production;
select detain.DateIngresoDetInv, inv.PK_codInv, inv.DescriptionInv, detailQuantityDetOrdMedida, detailCodUnMed, med.DescriptionAnMed, detailQuantityDetOrd, sum (cosG.TotalCosGas), sum (work.HoursHoHoWork), sum (work.CostoHoHo), COUNT (trab.CostoHoHo) from tbl_ordenproduction prod
INNER JOIN tbl_detalleordeproduction deta ON prod.PK_CodOrd = deta.CodOrd
INNER JOIN Tbl_Detail Inventory detainv ON detainv.FechaIngresoDetInv = prod.DateFinalizationOrd
INNER JOIN tbl_Inventory inv ON detainv.CodInv = inv.PK_CodInv
INNER JOIN tbl_unidadmedida med ON med.PK_CodUnMed = deta.CodUnMed
INNER JOIN tbl_receta rec ON inv.PK_CodInv = rec.CodInv
INNER JOIN tbl_costosgastosfabrication cosG ON rec.PK_CodRec = cosG.CodRec
INNER JOIN tbl_personalordenproduction by ON deta.CodOrd = por.CodOrd
INNER JOIN tbl_costoshorahombre trab ON por.PK_CodPerOrd = trab.CodPerOrd
WHERE month (detainv.FechaIngresoDetInv) = 03 and year (detainv.FechaIngresoDetInv) = 2019
AND deta.CodInv = inv.PK_CodInv
AND inv.PK_CodInv = 9
AND month (FechaCosGas) = 03 and year (FechaCosGas) = 2019
AND prod.PK_CodOrd = 10;
The values I give to the date, order and product are to test if the query works since I will send it from the program, but the problem is that it adds several times the same data, use a> count to see how many rows I took since there are only two rows that have the data but take them as 6, so I do not know what to do so that I do not take the same row several times and add those values every time I do the query. It takes the correct data but adds them several times.
These are the tables that I use:
create table TBL_Bodega (
PK_CodBod int,
NameBod varchar (45),
DirectionBod varchar (60),
int status,
PRIMARY KEY (PK_CodBod)
);
DROP TABLE IF EXISTS TBL_Measure Unit CASCADE;
create table TBL_UnityMeasure (
PK_CodUnMed int,
DescriptionUnMed varchar (25),
int status,
PRIMARY KEY (PK_CodUnMed)
);
DROP TABLE IF EXISTS TBL_Inventario CASCADE;
create table TBL_Inventario (
PK_CodInv int,
DescriptionInv varchar (45),
CostoInv float,
CodUnMed int,
int status,
PRIMARY KEY (PK_CodInv),
CONSTRAINT FK_CodUnMed_Inv
FOREIGN KEY (CodUnMed)
REFERENCES TBL_Measurement Unit (PK_CodUnMed)
);
DROP TABLE IF EXISTS TBL_Classification CASCADE;
create table TBL_Classification (
PK_CodClas int,
DescriptionVarchar beads (45),
int status,
PRIMARY KEY (PK_CodClas)
);
DROP TABLE IF EXISTS TBL_Department Inventory CASCADE;
create table TBL_DetailInventory (
CodInv int,
Int CodClas,
CodBod int,
DateIncomeDetInv date,
DateVentureDetInv date,
AmountDetInv int,
PRIMARY KEY (CodInv, CodClas, CodBod),
CONSTRAINT FK_CodInv_DetInv
FOREIGN KEY (CodInv)
REFERENCES TBL_Inventario (PK_CodInv),
CONSTRAINT FK_CodClas_DetInv
FOREIGN KEY (CodClas)
REFERENCES TBL_Classification (PK_CodClas),
CONSTRAINT FK_CodBod_DetInv
FOREIGN KEY (CodBod)
REFERENCES TBL_Bodega (PK_CodBod)
);
DROP TABLE IF EXISTS TBL_AuthorizationOrderProduction CASCADE;
create table TBL_AuthorizationOrderProduction (
PK_CodAut int,
DescriptionAut varchar (20),
int status,
PRIMARY KEY (PK_CodAut)
);
DROP TABLE IF EXISTS TBL_Avance CASCADE;
create table TBL_Avance (
PK_CodAva int,
DescriptionAva varchar (20),
int status,
PRIMARY KEY (PK_CodAva)
);
DROP TABLE IF EXISTS TBL_Priority CASCADE;
create table TBL_Priority (
PK_CodPrio int,
Description varchar (45),
int status,
PRIMARY KEY (PK_CodPrio)
);
DROP TABLE IF EXISTS TBL_OrderProduction CASCADE;
create table TBL_OrdenProduction (
PK_CodOrd int,
Start DateOrd date,
End DateOrd date,
CodAva int,
CodAut int,
CodPrio int,
int status,
PRIMARY KEY (PK_CodOrd),
CONSTRAINT FK_CodPrio_Ord
FOREIGN KEY (CodPrio)
REFERENCES TBL_Priority (PK_CodPrio),
CONSTRAINT FK_CodAut_Ord
FOREIGN KEY (CodAut)
REFERENCES TBL_AuthorizationOrderProduction (PK_CodAut),
CONSTRAINT FK_CodAva_Ord
FOREIGN KEY (CodAva)
REFERENCES TBL_Avance (PK_CodAva)
);
DROP TABLE IF EXISTS TBL_DetalleOrdenProduction CASCADE;
create table TBL_DetalleOrdenProduction (
CodOrd int,
CodInv int,
AmountDetOrd int,
AmountDetOrd float,
CodUnMed int,
PRIMARY KEY (CodOrd, CodInv),
CONSTRAINT FK_CodOrd_DetOrd
FOREIGN KEY (CodOrd)
REFERENCES TBL_OrderProduction (PK_CodOrd),
CONSTRAINT FK_CodInv_DetOrd
FOREIGN KEY (CodInv)
REFERENCES TBL_Inventario (PK_CodInv),
CONSTRAINT FK_CodUnMed_DetOrd
FOREIGN KEY (CodUnMed)
REFERENCES TBL_Measurement Unit (PK_CodUnMed)
);
DROP TABLE IF EXISTS TBL_Employee CASCADE;
create table TBL_Employee (
PK_CodEmp int,
NameEmp varchar (45),
SurnameEmp varchar (45),
SalaryEmp float,
NitEmp varchar (9),
DirectionEmp varchar (60),
Int phonephone,
MailEmp varchar (40),
int status,
PRIMARY KEY (PK_CodEmp)
);
DROP TABLE IF EXISTS TBL_PersonalOrderProduction CASCADE;
create table TBL_PersonalOrdenProduction (
PK_CodPerOrd int,
CodOrd int,
CodEmp int,
Hours workedPerOrd int,
AreaPerOrd varchar (20),
int status,
PRIMARY KEY (PK_CodPerOrd),
CONSTRAINT FK_CodOrd_PerOrd
FOREIGN KEY (CodOrd)
REFERENCES TBL_OrderProduction (PK_CodOrd),
CONSTRAINT FK_CodEmp_PerOrd
FOREIGN KEY (CodEmp)
REFERENCES TBL_Employee (PK_CodEmp)
);
DROP TABLE IF EXISTS TBL_Receta CASCADE;
create table TBL_Receta (
PK_CodRec int,
CodInv int, - Description of what product will be produced
AmountPersonalRec int,
HoursRequiredRec int,
int status,
PRIMARY KEY (PK_CodRec),
CONSTRAINT FK_CodInv_Rec
FOREIGN KEY (CodInv)
REFERENCES TBL_Inventario (PK_CodInv)
);
DROP TABLE IF EXISTS TBL_DetalleReceta CASCADE;
create table TBL_DetalleReceta (
CodRec int,
CodUnMed int,
CodInv int,
AmountDetRec int,
PRIMARY KEY (CodRec, CodInv),
CONSTRAINT FK_CodInv_DetRec
FOREIGN KEY (CodInv)
REFERENCES TBL_Inventario (PK_CodInv),
CONSTRAINT FK_CodRec_DetRec
FOREIGN KEY (CodRec)
REFERENCES TBL_Receta (PK_CodRec),
CONSTRAINT FK_CodUnMed_DetRec
FOREIGN KEY (CodUnMed)
REFERENCES TBL_Measurement Unit (PK_CodUnMed)
);
DROP TABLE IF EXISTS TBL_GastosFabrication CASCADE;
create table TBL_GastosFabricacion (
PK_CodGas int,
DescriptionGas varchar (30),
CodUnMed int,
int status,
PRIMARY KEY (PK_CodGas),
CONSTRAINT FK_CodUnMed_Gas
FOREIGN KEY (CodUnMed)
REFERENCES TBL_Measurement Unit (PK_CodUnMed)
);
DROP TABLE IF EXISTS TBL_CostosGastosFabrication CASCADE;
create table TBL_CostosGastosFabricacion (
PK_CodCosGas int,
CodRec int,
DateCosGas date,
TotalCosGas float,
int status,
PRIMARY KEY (PK_CodCosGas),
CONSTRAINT FK_CodRec_CosGas
FOREIGN KEY (CodRec)
REFERENCES TBL_Receta (PK_CodRec)
);
DROP TABLE IF EXISTS TBL_CostosHoraHombre CASCADE;
create table TBL_CostosHoraHombre (
PK_CodHoHo int,
CodPerOrd int,
CodEmp int,
Hours WorkedHoHo float,
CostHoHo float,
int status,
PRIMARY KEY (PK_CodHoHo),
CONSTRAINT FK_CodPerOrd_HoHo
FOREIGN KEY (CodPerOrd)
REFERENCES TBL_PersonalOrderProduction (PK_CodPerOrd),
CONSTRAINT FK_CodEmp_HoHo
FOREIGN KEY (CodEmp)
REFERENCES TBL_Employee (PK_CodEmp)
);
