Archiwa tagu: sum

Northwind 35. Wyświetl klientów którzy złożyli największą liczbę zamówień.

Baza : Northwind

Ćwiczenie nr: 35

Treść ćwiczenia: Wyświetl klientów którzy złożyli największą liczbę zamówień.

Polecenia/funkcje w zapytaniu: SELECT, SELECT TOP, FROM, WHERE, COUNT(), DECLARE SET, GROUP BY, ORDER BY


northwind tutorial practical exercises examples samples queries sql sql server  northwind tutorial practical exercises examples samples queries sql sql server 


Rozwiązanie:

Zapytanie:

Na początku zadeklarujmy sobie zmienną @ile do które "wrzucimy" największą liczbę zamówień, bo może się zdarzyć, że kilku klientów złożyło tyle samo zamówień.

DECLARE @ile int =
    (
        SELECT TOP 1
            COUNT(*)
        FROM    
            Orders O
        GROUP BY
            O.CustomerID
        ORDER BY
            COUNT(*) DESC
    );

Teraz utworzymy główne zapytanie w którym wykorzystamy zmienną @ile

SELECT
     O.CustomerID AS IdentyfikatorKlienta
    ,COUNT(*) AS Ile
FROM    
    Orders O
GROUP BY
    O.CustomerID
HAVING
    COUNT(*) = @ile

Wynik uruchomienia zapytania

Liczba rekordów: 1

northwind tutorial practical exercises examples samples queries sql sql server

Pobierz skrypt sql


northwind tutorial practical exercises examples samples queries sql sql server  northwind tutorial practical exercises examples samples queries sql sql server 


 

Northwind 34. Wyświetl informacje o pierwszym i ostatnim zamówieniu.

Baza : Northwind

Ćwiczenie nr: 34

Treść ćwiczenia: Wyświetl dane dotyczace pierwszego i ostatniego zamówienia. Zakres danych: Identyfikator zamówienia, Liczba pozycji w zamówieniu, Wartość zamówienia.

Polecenia/funkcje w zapytaniu: SELECT, SELECT TOP, FROM, WHERE, COUNT(), SUM(), INNER JOIN, GROUP BY, ORDER BY


northwind tutorial practical exercises examples samples queries sql sql server  northwind tutorial practical exercises examples samples queries sql sql server northwind tutorial practical exercises examples samples queries sql sql server


Rozwiązanie:

Zapytanie:

SELECT
          'Pierwsze zamówienie' AS Info
         ,D.OrderID AS IdentyfikatorZamowienia
         ,COUNT(*) AS LiczbaPozycji
         ,SUM(D.Quantity*D.UnitPrice) AS WartoscZamowienia
    FROM    
        (
        SELECT TOP 1
            O.OrderID
        FROM
            Orders O
        ORDER BY
            O.OrderDate, O.OrderID
        ) AS SQ
        JOIN [Order Details] D ON SQ.OrderID = D.OrderID
    GROUP BY
        D.OrderID
 
 UNION ALL
 
  SELECT
          'Ostatnie zamówienie' AS Info
         ,D.OrderID AS IdentyfikatorZamowienia
         ,COUNT(*) AS LiczbaPozycji
         ,SUM(D.Quantity*D.UnitPrice) AS WartoscZamowienia
    FROM    
        (
        SELECT TOP 1
            O.OrderID
        FROM
            Orders O
        ORDER BY
            O.OrderDate DESC, O.OrderID DESC
        ) AS SQ
        JOIN [Order Details] D ON SQ.OrderID = D.OrderID
    GROUP BY
        D.OrderID

Wynik uruchomienia zapytania

Liczba rekordów: 2

northwind tutorial practical exercises examples samples queries sql sql server

Pobierz skrypt sql


northwind tutorial practical exercises examples samples queries sql sql server  northwind tutorial practical exercises examples samples queries sql sql server northwind tutorial practical exercises examples samples queries sql sql server


 

SQL SUM() function

SQL SUM()


    


DEFINICJA

Funkcja SUM() zwraca sumę pól we wskazanej kolumnie o wartościach liczbowych.

SQL SUM() składnia.

SELECT
    SUM(nazwa_kolumny_z_wartościami_liczbowymi)
FROM
    nazwa_tabeli

 


Przykład zastosowania SUM() (baza Adventureworks)

Oblicz łączną wartość zamówień.

SELECT
    SUM(O.SubTotal) AS [Łączna wartość zamówień]
FROM
    Sales.SalesOrderHeader O


Przykład zastosowania SUM() (baza Northwind)

Oblicz łączną wartość zamówień klienta o ID WALLI.

SELECT
    SUM(D.Quantity * D.UnitPrice) AS [Łączna wartość zamówienia]
FROM
    Orders O JOIN [Order Details] D
        ON O.OrderID = D.OrderID
WHERE
    O.CustomerID = 'WELLI'


    

Adventureworks 9. Wyświetl informacje o zamówieniu (ID zamówienia , Wartość zamówienia) o największej wartości.

Baza: AdventureWorks

Zadanie nr: 9

Treść: Wyświetl informacje o zamówieniu (ID zamówienia , Wartość zamówienia) o największej wartości.

Polecenia/funkcje w zapytaniu: SELECT, SELECT TOP, FROM, Alias, INNER JOIN, GROUP BY, ORDER BY, SUM(), DESC


   


Rozwiązanie:

Zapytanie:

Wynik:

Pobierz skrypt sql


   


 

Adventureworks 25. Oblicz sumę zamówień o największej i najmniejszej wartości.

Baza : Adventureworks

Ćwiczenie nr: 25

Treść ćwiczenia: Oblicz sumę zamówień o największej i najmniejszej wartości.

Polecenia/funkcje w zapytaniu: SELECT, FROM, SUM(), TOP, Aliasy, GROUP BY, ORDER BY


   


Rozwiązanie:

 

Zapytanie:

SELECT
    (
        SELECT TOP 1
            SUM(D.OrderQty * D.UnitPrice)AS Suma
        FROM
            Sales.SalesOrderHeader Z JOIN Sales.SalesOrderDetail D
                ON Z.SalesOrderID = D.SalesOrderID
        GROUP BY
            Z.SalesOrderID        
        ORDER BY
            Suma DESC
    )
    +
    (
        SELECT TOP 1
            SUM(D.OrderQty * D.UnitPrice)AS Suma
        FROM
            Sales.SalesOrderHeader Z JOIN Sales.SalesOrderDetail D
                ON Z.SalesOrderID = D.SalesOrderID
        GROUP BY
            Z.SalesOrderID        
        ORDER BY
            Suma     
    )
    AS [SumaZamówień(Min.Max.)]   

Zdanie wyjaśnienia

to zadanie możemy rozwiązać na dwa sposoby.
1. Uwzględniając kwotę rabatu
2. Nie uwzględniając kwotę rabatu.

Powyższe zapytania nie uwzględnia kwoty rabatu. Jeżeli chcielibyśmy ją uwzględnić musimy posłużyć się innymi kolumnami. Jeżeli interesuje nas cena za wszystkie sztuki danego produktu w zamówieniu, to musimy posłużyć się kolumną "LineTotal" z tabeli "Sales.SalesOrderDetail", obliczenia dla tej kolumny uzyskujemy ze wzoru:

UnitPrice * (1-UnitPriceDiscount) * OrderQty

gdzie:
UnitPrice – to cena sprzedaży za sztukę produtku,
UnitPriceDiscount – to kwota rabatu a
OrderQty – to ilość zamówionych produktów.

czyli reasumując LineTotal to wartość zamówienia dla pojedynczego produktu w zamówieniu (uzwględniająca rabat i ilość zamówionego produktu).

Jeżeli interesuje nas ogólna wartość zamówienia (z uwzględnieniem rabatu) to możemy posłużyć się kolumną "SubTotal" a tabeli "Sales.SalesOrderHeader". Ta kolumną przechowuje wartości obliczone za pomocą formuły SUM(SalesOrderDetail.LineTotal)

Wynik uruchomienia zapytania

Liczba rekordów: 1

Pobierz skrypt sql


   


 

Northwind 25. Oblicz sumę zamówień o największej i najmniejszej wartości.

Baza : Northwind

Ćwiczenie nr: 25

Treść ćwiczenia: Oblicz sumę zamówień o największej i najmniejszej wartości.

Polecenia/funkcje w zapytaniu: SELECT, FROM, WHERE, Aliasy, SUM(), INNER JOIN, COUNT(), GROUP BY, ORDER BY


   


Rozwiązanie:

 

Zapytanie:

Wynik uruchomienia zapytania

Liczba rekordów: 1

Pobierz skrypt sql


   


 

Northwind 24. Który spedytor obsłużył zamówienia na największą wartość.

Baza : Northwind

Ćwiczenie nr: 24

Treść ćwiczenia: Który spedytor obsłużył zamówienia na największą wartość.

Polecenia/funkcje w zapytaniu: SELECT, FROM, WHERE, Aliasy, SUM(), INNER JOIN, COUNT(), GROUP BY, ORDER BY


   


Rozwiązanie:

 

Zapytanie:

Wynik uruchomienia zapytania

Liczba rekordów: 1 dla każdego sposobu

Pobierz skrypt sql


   


 

Adventureworks 30. Oblicz wartość każdego zamówienia.

Baza : Adventureworks

Ćwiczenie nr: 30

Treść ćwiczenia: Oblicz wartość każdego zamówienia.

Polecenia/funkcje w zapytaniu: SELECT, FROM, GROUP BY, ORDER BY, Aliasy, SUM(), INNER JOIN


   sql server adventureworks exercises queries


Rozwiązanie:

 

Zapytanie:

SELECT
     Z.SalesOrderID AS [ID Zamówienia]
    ,SUM(D.UnitPrice * D.OrderQty)AS [Wartość zamówienia]

FROM
    Sales.SalesOrderHeader Z JOIN Sales.SalesOrderDetail D
        ON     Z.SalesOrderID = D.SalesOrderID
 GROUP BY
    Z.SalesOrderID    
ORDER BY
    [ID Zamówienia]       

Wynik uruchomienia zapytania

Liczba rekordów: 31465

Pobierz skrypt sql


   sql server adventureworks exercises queries


 

Adventureworks 27a. Oblicz czy wartość zamówienia jest mniejsza czy większa/równa średniej wartość zamówień. Wykorzystaj deklarację zmiennej.

Baza : Adventureworks

Zadanie nr: 27a

Treść: Oblicz (w osobnej kolumnie) czy wartość zamówienia jest mniejsza (wartość 0) czy większa/równa (wartość 1) średniej wartość zamówień. Średnią wartość zamówień oblicz przed głównym zapytaniem i jej wartość "wrzuć" do zmiennej i wykorzystaj ją w zapytaniu głownym.

Polecenia/funkcje w zapytaniu: SELECT, FROM, CASE, SUM(), AVG(), GROUP BY, DECLARE, SET


   


Rozwiązanie:

 

Zapytanie:

DECLARE @srednia float;
SET @srednia = (SELECT
                    AVG(Suma) AS ŚredniaWartośćZamówienia
                FROM
                    (    
                        SELECT
                            SalesOrderID
                            ,SUM(UnitPrice)AS Suma
                        FROM
                            Sales.SalesOrderDetail
                        GROUP BY
                            SalesOrderID)AS query);
 
SELECT
    SalesOrderID
    ,SUM(UnitPrice)AS Suma
    ,CASE
        WHEN SUM(UnitPrice)< (@srednia) THEN 0             
        ELSE 1                
        END    AS [0-mniejsze niż śr./1-większe,równe śr]                                
FROM
    Sales.SalesOrderDetail
GROUP BY
    SalesOrderID   

Wynik uruchomienia zapytania

Liczba rekordów: 31465

Pobierz skrypt sql