📊 Agregātfunkcijas un grupēšana

-- COUNT – skaitīt rindas
SELECT COUNT(*)       FROM products;          -- Visas rindas
SELECT COUNT(id)      FROM products;          -- Rindas, kur id nav NULL
SELECT COUNT(DISTINCT category) FROM products; -- Unikālo kategoriju skaits

-- SUM – summa
SELECT SUM(price)  FROM products;
SELECT SUM(price)  FROM products WHERE category = 'Elektronika';

-- AVG – vidējā vērtība
SELECT AVG(price)           FROM products;
SELECT ROUND(AVG(price), 2) FROM products;   -- Noapaļot uz 2 zīmēm

-- MAX un MIN
SELECT MAX(price)       FROM products;   -- Dārgākais
SELECT MIN(price)       FROM products;   -- Lētākais
SELECT MAX(created_at)  FROM orders;     -- Jaunākais pasūtījums
SELECT MIN(created_at)  FROM orders;     -- Vecākais pasūtījums

-- Visi kopā
SELECT
    COUNT(*)         AS kopā,
    MIN(price)       AS lētākais,
    MAX(price)       AS dārgākais,
    ROUND(AVG(price), 2) AS vidējā_cena,
    SUM(price)       AS kopējā_vērtība
FROM products
WHERE in_stock = 1;

-- Produktu skaits pa kategorijām
SELECT category, COUNT(*) AS skaits
FROM products
GROUP BY category;

-- Vidējā cena pa kategorijām, sakārtots
SELECT
    category,
    COUNT(*)             AS skaits,
    ROUND(AVG(price), 2) AS vide_cena,
    MIN(price)           AS lētākais,
    MAX(price)           AS dārgākais
FROM products
GROUP BY category
ORDER BY vide_cena DESC;

-- Pārdošana pa gadiem/mēnešiem
SELECT
    YEAR(created_at)  AS gads,
    MONTH(created_at) AS mēnesis,
    SUM(total)        AS kopā,
    COUNT(*)          AS pasūtījumi
FROM orders
GROUP BY YEAR(created_at), MONTH(created_at)
ORDER BY gads DESC, mēnesis DESC;

-- Pasūtījumu skaits pa lietotājiem
SELECT
    user_id,
    COUNT(*)   AS pasūtījumu_skaits,
    SUM(total) AS kopējā_summa
FROM orders
GROUP BY user_id
ORDER BY kopējā_summa DESC;

-- GROUP BY ar JOIN (lietotāja vārds)
SELECT
    u.username,
    COUNT(o.id)   AS pasūtījumi,
    SUM(o.total)  AS kopā_€
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username
ORDER BY kopā_€ DESC;

-- Kategorijas, kurās ir vairāk nekā 5 produkti
SELECT category, COUNT(*) AS skaits
FROM products
GROUP BY category
HAVING skaits > 5;          -- HAVING izmanto aliasu no SELECT

-- HAVING ar agregātfunkciju tieši
SELECT category, COUNT(*) AS skaits
FROM products
GROUP BY category
HAVING COUNT(*) >= 3
ORDER BY skaits DESC;

-- WHERE + HAVING kombinācija
-- WHERE filtrē PIRMS grupēšanas, HAVING – PĒC
SELECT
    category,
    COUNT(*)         AS skaits,
    AVG(price)       AS vidusmērs
FROM products
WHERE in_stock = 1               -- Vispirms filtrē pieejamos
GROUP BY category
HAVING AVG(price) > 50           -- Tad atstāj kategorijas ar vidējo cenu > 50
ORDER BY vidusmērs DESC;

-- Lietotāji, kas pasūtījuši vairāk nekā 3 reizes
SELECT user_id, COUNT(*) AS reizes
FROM orders
GROUP BY user_id
HAVING reizes > 3;

-- Dienas ar vairāk nekā 10 pasūtījumiem
SELECT DATE(created_at) AS datums, COUNT(*) AS pasūtājumi
FROM orders
GROUP BY DATE(created_at)
HAVING pasūtājumi > 10
ORDER BY datums DESC;

-- TEKSTS
SELECT UPPER(name) FROM products;               -- LIELOS BURTUS
SELECT LOWER(email) FROM users;                  -- mazos burtus
SELECT LENGTH(name) FROM products;               -- Rakstzīmju skaits
SELECT CHAR_LENGTH(name) FROM products;          -- UTF-8 drošs garums
SELECT TRIM(name) FROM products;                 -- Noņemt sākuma/beigu atstarpes
SELECT LTRIM(name) FROM products;                -- Tikai kreiso
SELECT RTRIM(name) FROM products;                -- Tikai labo

SELECT SUBSTRING(name, 1, 10) FROM products;     -- Pirmie 10 simboli
SELECT LEFT(name, 5)   FROM products;             -- Pirmie 5
SELECT RIGHT(name, 5)  FROM products;             -- Pēdējie 5

SELECT REPLACE(name, 'Pro', 'Professional') FROM products;
SELECT CONCAT(first_name, ' ', last_name)  AS pilnais FROM users;
SELECT CONCAT_WS(', ', city, country) AS adrese FROM contacts;

-- DATUMS
SELECT NOW()          AS tagad;          -- 2025-03-06 14:30:00
SELECT CURDATE()      AS šodien;         -- 2025-03-06
SELECT CURTIME()      AS laiks;          -- 14:30:00

SELECT DATE(created_at)   FROM orders;   -- Tikai datuma daļa
SELECT YEAR(created_at)   FROM orders;   -- 2025
SELECT MONTH(created_at)  FROM orders;   -- 3
SELECT DAY(created_at)    FROM orders;   -- 6
SELECT DAYNAME(created_at) FROM orders;  -- Thursday

SELECT DATE_FORMAT(created_at, '%d.%m.%Y') AS formatets FROM orders;

-- Datumu aritmētika
SELECT DATE_ADD(NOW(), INTERVAL 7  DAY)   AS pec_nedelas;
SELECT DATE_SUB(NOW(), INTERVAL 30 DAY)   AS pirms_menesa;
SELECT DATEDIFF('2025-12-31', NOW())      AS dienas_lidz_gada_beigas;

-- Filtrēt pēdējo mēnesi
SELECT * FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);