SQL commands with subtraction and multiplication

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

Discussion Overview

The discussion revolves around SQL commands related to a project administration database. Participants are tasked with writing SQL queries to extract information about employees, projects, and competencies based on various criteria. The focus includes general applicability of the queries and the correct use of SQL functions.

Discussion Character

  • Technical explanation
  • Debate/contested
  • Mathematical reasoning

Main Points Raised

  • Some participants propose SQL queries to identify employees assigned to specific projects, while others question the correctness of using hardcoded project identifiers.
  • There is a discussion about calculating sales based on planned days and daily rates, with participants expressing uncertainty about the use of the DATEDIFF function.
  • Participants raise concerns about the uniqueness of primary keys in the database schema and the implications for query accuracy.
  • Some suggest that queries should be adaptable to different project names, prompting discussions on how to structure SQL commands to achieve this flexibility.
  • There are multiple suggestions for using nested selects or other SQL constructs to generalize queries, but participants express confusion about the implementation details.

Areas of Agreement / Disagreement

Participants generally agree on the need for SQL queries to be adaptable to different inputs, but there is no consensus on the best methods to achieve this or the correctness of specific queries proposed.

Contextual Notes

Participants highlight limitations regarding the primary key definitions and the need for queries to be applicable to various project names, indicating potential issues with hardcoded values in SQL commands.

Who May Find This Useful

This discussion may be useful for individuals interested in SQL query formulation, database management, and those seeking to understand best practices for writing adaptable SQL commands.

  • #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)