Az összesítést olyan táblán érdemes megtanulni, ahol számolni is lehet, ezért készítünk egy új, kis gyakorló adatbázist. A DB Browser programban (3.1 lecke) hozz létre egy új adatbázist gyakorlo.db néven, nyisd meg az Execute SQL fület, másold be az alábbi kódot, futtasd (Ctrl+Enter), végül mentsd a változásokat (Write Changes, Ctrl+S).

CREATE TABLE IF NOT EXISTS Dolgozok (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nev TEXT NOT NULL,
    reszleg TEXT NOT NULL,
    fizetes INTEGER NOT NULL,
    premium INTEGER
);

INSERT INTO Dolgozok (nev, reszleg, fizetes, premium) VALUES
    ('Kiss Anna', 'Iroda', 420000, 50000),
    ('Nagy Béla', 'Iroda', 380000, NULL),
    ('Tóth Cili', 'Raktár', 310000, NULL),
    ('Szabó Dénes', 'Raktár', 330000, 20000),
    ('Horváth Eszter', 'Raktár', 295000, NULL),
    ('Varga Feri', 'Műhely', 450000, 80000),
    ('Kovács Gréta', 'Műhely', 410000, NULL),
    ('Farkas Hugó', 'Iroda', 365000, 30000);

A táblában nyolc ember van három részlegen: az Irodában három, a Raktárban három, a Műhelyben két ember dolgozik.

Az összesítő függvény sok sort egyetlen értékké von össze. Eddig csak a COUNT(*)-ot láttad (3.1). A többi is hasonlóan működik:

SELECT COUNT(*)      AS db,
       SUM(fizetes)  AS osszeg,
       AVG(fizetes)  AS atlag,
       MIN(fizetes)  AS legkisebb,
       MAX(fizetes)  AS legnagyobb
FROM Dolgozok;

A prémium oszlopban nem mindenkinek van érték. Nézzük meg, mit csinálnak az összesítő függvények a NULL értékekkel:

SELECT COUNT(*)       AS mindenki,
       COUNT(premium) AS premiumosok,
       SUM(premium)   AS premiumOsszeg
FROM Dolgozok;

Amikor az összeg is NULL

Ha egy összesítésnél egyetlen sor sem felel meg a feltételnek, a SUM eredménye nem nulla, hanem NULL. Próbáld ki:

SELECT SUM(premium) FROM Dolgozok WHERE reszleg = 'Nincs ilyen';

A DB Browser egy NULL cellát mutat. Egy C# programban ez gondot okoz, mert a NULL értéket nem lehet számmá alakítani (lásd a Gyakori hibák szakaszt). A megoldás a COALESCE:

SELECT COALESCE(SUM(premium), 0) FROM Dolgozok WHERE reszleg = 'Nincs ilyen';

A COALESCE a sorok szintjén is hasznos. Ha a fizetéshez hozzá akarod adni a prémiumot, a NULL prémiumot nullának kell venni, különben az egész összeg NULL lenne:

SELECT nev, fizetes + COALESCE(premium, 0) AS teljes
FROM Dolgozok
ORDER BY teljes DESC;

Az eddigi lekérdezések az egész táblát egyetlen sorba vonták össze. A GROUP BY azt mondja: csoportosítsd a sorokat egy oszlop értéke szerint, és minden csoportra külön számolj. Így kapsz részlegenként összegeket.

SELECT reszleg,
       COUNT(*)      AS fo,
       SUM(fizetes)  AS osszeg
FROM Dolgozok
GROUP BY reszleg;

A lekérdezés sorai: WHERE, GROUP BY, ORDER BY

A három záradék mindig ebben a sorrendben áll: először a WHERE szűr, utána a GROUP BY csoportosít, végül az ORDER BY rendez. Például a legalább 350 000 Ft-ot keresők összege részlegenként, a legnagyobb összeggel kezdve:

SELECT reszleg, SUM(fizetes) AS osszeg
FROM Dolgozok
WHERE fizetes >= 350000
GROUP BY reszleg
ORDER BY osszeg DESC;

Az eredmény két sor: Iroda 1 165 000, Műhely 860 000. A Raktárban senki sem keres ennyit, ezért az a csoport meg sem jelenik.

Rendezés összeg szerint, döntetlennel

Az ORDER BY az összesítés eredményére is hivatkozhat az aliasával. Ha két csoport összege egyenlő, érdemes egy második rendezési szempontot is adni, hogy a sorrend mindig ugyanaz legyen:

SELECT reszleg, COUNT(*) AS fo
FROM Dolgozok
GROUP BY reszleg
ORDER BY fo DESC, reszleg;

Az Iroda és a Raktár is három fős, ezért a második szempont (a név betűrendje) dönt: Iroda 3, Raktár 3, Műhely 2.

Most vigyük át ugyanezt egy programba. Két módon kapsz eredményt: egy egyetlen számot (összeg) az ExecuteScalar ad vissza, egy kis táblát (részlegenként) pedig az SQLiteDataAdapter tölt be, mint a 3.8 leckében.

Előkészítés

1. Hozz létre egy új Windows Forms (.NET Framework) projektet Osszesites néven, és telepítsd a System.Data.SQLite csomagot a NuGettel (3.2 lecke).
2. Add hozzá a DB.cs osztályt a 3.2 leckéből, de az adatbázis fájlneve legyen "gyakorlo.db".
3. Másold a DB Browserben mentett gyakorlo.db fájlt a projekt bin\Debug mappájába.
4. A Formra tegyél egy Button-t (btnOsszeg), egy Label-t (lblEredmeny), egy másik Button-t (btnReszlegek), és egy DataGridView-t (dgvEredmeny, ReadOnly = true).

Egy szám: ExecuteScalar

private void btnOsszeg_Click(object sender, EventArgs e)
{
    try
    {
        using (SQLiteConnection kapcsolat = DB.UjKapcsolat())
        {
            kapcsolat.Open();

            using (SQLiteCommand parancs = new SQLiteCommand("SELECT COALESCE(SUM(fizetes), 0) FROM Dolgozok", kapcsolat))
            {
                int osszeg = Convert.ToInt32(parancs.ExecuteScalar());
                lblEredmeny.Text = "Összes fizetés: " + osszeg + " Ft";
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Hiba: " + ex.Message);
    }
}

Egy kis tábla: részlegenként

private void btnReszlegek_Click(object sender, EventArgs e)
{
    string sql = "SELECT reszleg AS Reszleg, COUNT(*) AS Fo, SUM(fizetes) AS Osszeg " +
                 "FROM Dolgozok GROUP BY reszleg ORDER BY Osszeg DESC, Reszleg";

    try
    {
        DataTable tabla = new DataTable();

        using (SQLiteConnection kapcsolat = DB.UjKapcsolat())
        using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, kapcsolat))
        {
            adapter.Fill(tabla);
        }

        dgvEredmeny.DataSource = tabla;
    }
    catch (Exception ex)
    {
        MessageBox.Show("Hiba: " + ex.Message);
    }
}

A táblázat fejlécei az SQL-ben megadott aliasok (Reszleg, Fo, Osszeg). Az eredmény három sor: Iroda 3 fő, 1 165 000; Raktár 3 fő, 935 000; Műhely 2 fő, 860 000.

Paraméterrel szűrt összeg

A szűrő értékét (például egy NumericUpDown-ból) paraméterként add át, ahogy a 3.7 leckében tanultad:

private void btnSzurt_Click(object sender, EventArgs e)
{
    try
    {
        using (SQLiteConnection kapcsolat = DB.UjKapcsolat())
        {
            kapcsolat.Open();

            using (SQLiteCommand parancs = new SQLiteCommand("SELECT COALESCE(SUM(fizetes), 0) FROM Dolgozok WHERE fizetes >= @kuszob", kapcsolat))
            {
                parancs.Parameters.AddWithValue("@kuszob", (int)nudKuszob.Value);

                int osszeg = Convert.ToInt32(parancs.ExecuteScalar());
                lblEredmeny.Text = "A küszöb feletti fizetések összege: " + osszeg + " Ft";
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Hiba: " + ex.Message);
    }
}

Ha a nudKuszob értéke 350000 (állítsd be a Maximum-ot elég nagyra, például 1000000-ra), az eredmény 2 025 000 Ft: az Iroda 1 165 000 és a Műhely 860 000 összege.

Előbb próbáld meg magad, aztán nyisd le a megoldást. Mindegyik a Dolgozok táblát használja.

SELECT reszleg, MAX(fizetes) AS legtobb
FROM Dolgozok
GROUP BY reszleg
ORDER BY reszleg;

SELECT nev, fizetes
FROM Dolgozok
ORDER BY fizetes DESC, nev
LIMIT 1;

SELECT reszleg, SUM(fizetes + COALESCE(premium, 0)) AS teljes
FROM Dolgozok
GROUP BY reszleg
ORDER BY teljes DESC, reszleg;

private void btnPremiumosok_Click(object sender, EventArgs e)
{
    string sql = "SELECT reszleg AS Reszleg, COUNT(premium) AS Premiumosok " +
                 "FROM Dolgozok GROUP BY reszleg ORDER BY Reszleg";

    try
    {
        DataTable tabla = new DataTable();

        using (SQLiteConnection kapcsolat = DB.UjKapcsolat())
        using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, kapcsolat))
        {
            adapter.Fill(tabla);
        }

        dgvEredmeny.DataSource = tabla;
    }
    catch (Exception ex)
    {
        MessageBox.Show("Hiba: " + ex.Message);
    }
}