MHB SQL commands with subtraction and multiplication

[mathmari]

We are trying to group the 2 rows that refer to Armin into 1 row.
Those 2 rows have different numbers for the Umsatz.
How should those 2 numbers be combined (aka aggregated)?

We have to add the corresponding values of Umsatz, right? So do we write something like :

SELECT DISTINCT m.vorname, m.name, DATEDIFF(day, z.datumvon, z.datumbis)*m.tagessatz AS Umsatz
FROM mitarb m, projektzuord z
WHERE m.mnr = z.mitarbnr AND m.kompetenz = z.kompetenz
GROUP BY m.vorname, m.name,SUM(DATEDIFF(day, z.datumvon, z.datumbis)*m.tagessatz) :unsure:

 

[I like Serena]

We have to add the corresponding values of Umsatz, right? So do we write something like

Something like that yes (Nod)

Does it work?

 

[mathmari]

Something like that yes (Nod)

Does it work?

No, I get an error : I get this :unsure:

 

[I like Serena]

No, I get an error : I get this

It says:
> Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Perhaps we shouldn't put the SUM in the GROUP BY clause, but somewhere else?
Can we find an example how to use a GROUP BY clause?

 

[mathmari]

It says:
> Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Perhaps we shouldn't put the SUM in the GROUP BY clause, but somewhere else?
Can we find an example how to use a GROUP BY clause?

Ah I fixed it ! Now it works!
Is my below attempt correct ? :unsure:

At (3) :

SELECT p.projektbez
FROM projektzuord z, projekte p
GROUP BY p.projektbez, p.projnr
HAVING COUNT(p.projektbez)>=2 AND p.projnr = ANY (SELECT z.projnr FROM projektzuord z)At (4) :

SELECT COUNT(m.mnr), m.kompetenz
FROM projektzuord z, mitarb m
WHERE ((z.datumbis $<$ '20220315' OR z.datumvon $>$ '20220409') AND (m.mnr = z.mitarbnr)) OR m.mnr != ALL (SELECT z.mitarbnr FROM projektzuord z)
GROUP BY m.mnr, m.kompetenzAt (5) do we make INSERT the new values in the corresponding tables?

INSERT INTO projekte
VALUES
('p42', 'Datenreorganisation', 'kn99', 12000);

INSERT INTO projektzuord
VALUES
('p42', 123, 'k3', '2022-03-01', '2022-05-31');
:unsure:

 

[I like Serena]

3) Which competencies (designation of competency) will be needed at least twice in the planned projects ?

SELECT p.projektbez
FROM projektzuord z, projekte p
GROUP BY p.projektbez, p.projnr
HAVING COUNT(p.projektbez)>=2 AND p.projnr = ANY (SELECT z.projnr FROM projektzuord z)

Shouldn't we list kompbez instead of projektbez? (Worried)