Select multiplication by count SQL, Group By, Oracle

Question

I have this tables....

TableHead tblHead
 - Date schedule
 - Number idCode

Date       idCode
2018/05/12 10007
2018/05/15 10008
2018/09/18 19892
2018/11/20 19897
2018/05/15 23404

TableDetail tblDetail
 - Number headCode
 - Varchar2 superService

headCode superService
10007    S67900
10008    S89333
10008    S89335
19892    A45899
19892    P41899
19897    A89320
23404    S89333
23404    P41899

TableInternal
 - Varchar2 superService
 - Varchar2 nameService
 - Number(15,2) quantity

superService  nameService  quantity
S89333        1000045677   2.1
S89335        3002344444   0.7
P41899        1000045677   5.6
P41899        2000045679   4.3

The TableHead table has a Date field, and TableDetail has foreign a key to itsidCode. Now TableDetail has a Service field with the name superService like a package.

The package is described by the TableInternal table, with quantity field, and contained nameService field.

In other words... the superService field contains quantity times nameService

I want to Obtain Example for findByDate=2018/05/15, then the codes 10008 and 23404 from TableHead must be processed.

Now, 1008 code has S89333 and S89335, and 23404 has S89333 and P41899.

Resuming, I need to process two times S89333 and one time S89335 and P41899

Now, for Detailed Service...

nameService   schedule    Total
1000045677    2018/05/15  2*2.1 + 5.6
3002344444    2018/05/15  0.7
2000045679    2018/05/15  4.3  

My Query is Wrong... but, I don't know how to do it,

SELECT 
tblInt.nameService, TO_DATE(TO_CHAR(tblHead.schedule,'dd/MM/yy')), COUNT (*) subTotal
FROM TableDetail tblDetail,  TableHead tblHead, TableInternal tblInt
WHERE tblHead.idCode = tblDetail.headCode
AND tblDetail.superService = tblInt.superService
AND TO_CHAR(tblHead.schedule,'dd/MM/yy') =:findByDate
GROUP BY 
tblInt.nameService, TO_CHAR(tblHead.schedule,'dd/MM/yy')
ORDER BY TO_DATE(TO_CHAR(tblHead.schedule,'dd/MM/yy')) DESC;

Now I want to obtain a Total field where value is subTotal * tblInt.quantity in the same Query.

But, I don't know how to do it.

My Question How Can to multiply the count by another numeric field (using group by)?

Answer 1

If I didn't get any thing wrong you just need to join all of it together, group it and you have your sum. You don't need to multiply by a count because this is implicitly done. The rows for the respective (super?)service (I don't get the entities) appears twice in the ungrouped set. And of course 2.1 + 2.1 = 2 * 2.1.

SELECT i.nameservice,
       h.schedule,
       sum(i.quantity) total
       FROM tablehead h
            INNER JOIN tabledetail d
                       ON d.headcode = h.idcode
            INNER JOIN tableinternal i
                       ON i.superservice = d.superservice
       WHERE h.schedule = to_date('2018-05-15', 'YYYY-MM-DD')
       GROUP BY i.nameservice,
                h.schedule;

db<>fiddle