Request for data sample sql



  • For each post, calculate the number of staff and the number of orders.

    As long as I'm struggling with the withdrawal of posts and the number of staff for each post. That's what to get. ♪ ♪

    Naimenovanie... ♪ ♪ ♪ ♪ ♪ ♪ Kolichestvo

    Waiter/corier. ♪ ♪ ♪ 5

    Dolzhnosti

    Sotrudniki

    FormirovaneiZakaza

    SELECT
      Dolzhnosti.Naimenovanie,
      COUNT(Sotrudniki.Kod_dolzhnosti) AS Kolichestvo
    FROM
      Dolzhnosti
    INNER JOIN
     (FormirovanieZakaza
      INNER JOIN
        Sotrudniki
      ON
        FormirovanieZakaza.Kod_sotrudnika = Sotrudniki.Kod_sotrudnika)
    ON
      Dolzhnosti.Kod_dolzhnosti = Sotrudniki.Kod_dolzhnosti
    GROUP BY
      Dolzhnosti.Naimenovanie;
    


  • SELECT
      Dolzhnosty.Kod_dolzhnosty,
      COALESCE(T.kolichestvo_sotr, 0) as kolichestvo_sotr, 
      COUNT(*) as kolichestvo_zakazov
    FROM Dolzhnosty 
      --LEFT нужны для того, чтобы не потерять должности, у которых нет сотрудников 
      --или есть сотрудники, которые не формировали заказы.
      LEFT JOIN (
        SELECT Kod_dolzhnosty, COUNT(*) as kolichestvo_sotr
        FROM Sotrudniki
        GROUP BY Kod_dolzhnosty
      ) as T ON Dolzhnosty.Kod_dolzhnosty = T.Kod_dolzhnosty
      LEFT JOIN Sotrudniki ON Dolzhnosty.Kod_dolzhnosty = Sotrudniki.Kod_dolzhnosty
      LEFT JOIN FormirovanieZakaza ON FormirovanieZakaza.Kod_sotrudnika = Sotrudniki.Kod_sotrudnika
    GROUP BY Dolzhnosty.Kod_dolzhnosty
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2