SQL commands with subtraction and multiplication

  • Context: MHB 
  • Thread starter Thread starter mathmari
  • Start date Start date
  • Tags Tags
    Multiplication Sql
Click For Summary
SUMMARY

This discussion focuses on SQL commands involving subtraction and multiplication in the context of project management data. Key tasks include retrieving employee names assigned to specific projects, calculating sales based on planned days and daily rates, identifying competencies needed for projects, and checking employee availability within a date range. The participants emphasize the importance of writing general SQL statements that can adapt to different project names and highlight issues with using the DATEDIFF function in various SQL environments.

PREREQUISITES
  • Understanding of SQL syntax and commands
  • Familiarity with relational database concepts, including primary keys
  • Knowledge of SQL functions such as DATEDIFF and GROUP BY
  • Experience with SQL environments like PostgreSQL and SQL Server
NEXT STEPS
  • Learn how to use SQL subqueries effectively
  • Explore the differences in SQL functions across various database systems, such as PostgreSQL and SQL Server
  • Study how to write adaptable SQL queries that can handle dynamic input
  • Investigate best practices for calculating turnover and handling date calculations in SQL
USEFUL FOR

Database developers, data analysts, and project managers looking to optimize SQL queries for project management and employee assignment analysis.

  • #31
Klaas van Aarsen said:
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:
 
Physics news on Phys.org
  • #32
mathmari said:
We have to add the corresponding values of Umsatz, right? So do we write something like
Something like that yes (Nod)

Does it work? 🤔
 
  • #33
Klaas van Aarsen said:
Something like that yes (Nod)

Does it work? 🤔

No, I get an error : I get this :unsure:
 
  • #34
mathmari said:
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? 🤔
 
  • #35
Klaas van Aarsen said:
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:
 
Last edited by a moderator:
  • #36
mathmari said:
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)