📊 GROUP BY va Aggregation - COUNT, SUM, AVG, MIN, MAX

-- Barcha qatorlar soni
SELECT COUNT(*) FROM users;

-- NULL bo'lmagan qiymatlar soni
SELECT COUNT(email) FROM users;

-- Takrorlanmas qiymatlar soni
SELECT COUNT(DISTINCT category) FROM products;

-- Barcha narxlarning yig'indisi
SELECT SUM(price) FROM products;

-- Belgili shart bilan
SELECT SUM(price) FROM products WHERE category = 'Electronics';

-- Murakkab hisob-kitob
SELECT SUM(price * quantity) FROM order_items;

-- O'rtacha narx
SELECT AVG(price) FROM products;

-- Belgili shart bilan
SELECT AVG(age) FROM users WHERE age > 18;

-- Murakkab hisob-kitob
SELECT AVG(price * quantity) FROM order_items;

-- Eng kichik narx
SELECT MIN(price) FROM products;

-- Eng katta narx
SELECT MAX(price) FROM products;

-- Eng kichik yosh
SELECT MIN(age) FROM users;

-- Eng katta yosh
SELECT MAX(age) FROM users;

-- Kategoriya bo'yicha guruhlash
SELECT category, COUNT(*) FROM products GROUP BY category;

-- Kategoriya bo'yicha o'rtacha narx
SELECT category, AVG(price) FROM products GROUP BY category;

-- Kategoriya bo'yicha jami narx
SELECT category, SUM(price) FROM products GROUP BY category;

-- Kutubxona tizimi
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    publication_year INTEGER,
    pages INTEGER,
    price REAL,
    category TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE book_loans (
    id INTEGER PRIMARY KEY,
    book_id INTEGER,
    borrower_name TEXT,
    loan_date DATE,
    return_date DATE,
    is_returned BOOLEAN DEFAULT 0,
    FOREIGN KEY (book_id) REFERENCES books(id)
);

-- Ma'lumotlar
INSERT INTO books (title, author, publication_year, pages, price, category) VALUES 
('Python Programming', 'John Doe', 2020, 300, 45.99, 'Programming'),
('JavaScript Guide', 'Jane Smith', 2019, 250, 35.50, 'Programming'),
('History of Uzbekistan', 'Ahmad Karimov', 2018, 400, 25.00, 'History'),
('O''zbek tili', 'Malika Toshmatova', 2021, 200, 15.75, 'Language'),
('Data Science', 'Bobur Valiyev', 2022, 350, 55.00, 'Programming'),
('Advanced Python', 'John Doe', 2021, 400, 60.00, 'Programming');

INSERT INTO book_loans (book_id, borrower_name, loan_date, return_date, is_returned) VALUES 
(1, 'Ali Valiyev', '2023-01-15', '2023-02-15', 1),
(2, 'Malika Karimova', '2023-02-01', NULL, 0),
(3, 'Bobur Toshmatov', '2023-01-20', '2023-02-20', 1),
(1, 'Zarina Qodirova', '2023-03-01', NULL, 0),
(4, 'Ahmad Karimov', '2023-02-15', '2023-03-15', 1);

-- Aggregation misollari
-- 1. Barcha kitoblar soni
SELECT COUNT(*) FROM books;

-- 2. Kategoriya bo'yicha kitoblar soni
SELECT category, COUNT(*) FROM books GROUP BY category;

-- 3. Muallif bo'yicha kitoblar soni
SELECT author, COUNT(*) FROM books GROUP BY author;

-- 4. Kategoriya bo'yicha o'rtacha narx
SELECT category, AVG(price) FROM books GROUP BY category;

-- 5. Kategoriya bo'yicha jami narx
SELECT category, SUM(price) FROM books GROUP BY category;

-- 6. Kategoriya bo'yicha eng qimmat kitob
SELECT category, MAX(price) FROM books GROUP BY category;

-- 7. Kategoriya bo'yicha eng arzon kitob
SELECT category, MIN(price) FROM books GROUP BY category;

-- Bir nechta ustun bo'yicha guruhlash
SELECT category, author, COUNT(*) 
FROM books 
GROUP BY category, author;

-- Murakkab aggregation
SELECT 
    category,
    COUNT(*) as total_books,
    AVG(price) as avg_price,
    MIN(price) as min_price,
    MAX(price) as max_price,
    SUM(price) as total_value
FROM books 
GROUP BY category;

-- Shart bilan guruhlash
SELECT 
    category,
    COUNT(*) as books_count,
    AVG(price) as avg_price
FROM books 
WHERE publication_year > 2019
GROUP BY category;

-- HAVING - guruh shartlari
SELECT category, COUNT(*) 
FROM books 
GROUP BY category 
HAVING COUNT(*) > 1;

-- Murakkab HAVING
SELECT 
    category,
    AVG(price) as avg_price,
    COUNT(*) as books_count
FROM books 
GROUP BY category 
HAVING AVG(price) > 30 AND COUNT(*) > 1;

-- WHERE va HAVING farqi
SELECT 
    category,
    COUNT(*) as books_count
FROM books 
WHERE publication_year > 2019  -- Qator sharti
GROUP BY category 
HAVING COUNT(*) > 1;  -- Guruh sharti

-- E-commerce tizimi
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    total_amount REAL,
    status TEXT
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    price REAL,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- Ma'lumotlar
INSERT INTO orders (customer_id, order_date, total_amount, status) VALUES 
(1, '2023-01-15', 150.00, 'completed'),
(2, '2023-01-16', 200.00, 'completed'),
(1, '2023-01-17', 100.00, 'pending'),
(3, '2023-01-18', 300.00, 'completed'),
(2, '2023-01-19', 250.00, 'completed');

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES 
(1, 1, 2, 50.00),
(1, 2, 1, 50.00),
(2, 1, 1, 50.00),
(2, 3, 3, 50.00),
(3, 2, 2, 50.00),
(4, 1, 3, 50.00),
(4, 2, 2, 50.00),
(4, 3, 1, 50.00),
(5, 1, 1, 50.00),
(5, 3, 4, 50.00);

-- E-commerce analytics
-- 1. Har bir mijozning buyurtmalari soni
SELECT customer_id, COUNT(*) as order_count
FROM orders 
GROUP BY customer_id;

-- 2. Har bir mijozning jami xarid summasi
SELECT customer_id, SUM(total_amount) as total_spent
FROM orders 
GROUP BY customer_id;

-- 3. Har bir mahsulotning sotilgan miqdori
SELECT product_id, SUM(quantity) as total_sold
FROM order_items 
GROUP BY product_id;

-- 4. Har bir mahsulotning jami daromadi
SELECT product_id, SUM(quantity * price) as total_revenue
FROM order_items 
GROUP BY product_id;

-- 5. Status bo'yicha buyurtmalar
SELECT status, COUNT(*) as order_count, AVG(total_amount) as avg_amount
FROM orders 
GROUP BY status;

-- 6. Oy bo'yicha daromad
SELECT 
    strftime('%Y-%m', order_date) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_revenue
FROM orders 
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

-- 7. Eng ko'p xarid qilgan mijozlar
SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent
FROM orders 
GROUP BY customer_id 
HAVING COUNT(*) > 1
ORDER BY total_spent DESC;

-- Murakkab aggregation
SELECT 
    category,
    COUNT(*) as total_books,
    COUNT(DISTINCT author) as unique_authors,
    AVG(price) as avg_price,
    MIN(price) as min_price,
    MAX(price) as max_price,
    SUM(price) as total_value,
    AVG(pages) as avg_pages
FROM books 
GROUP BY category;

-- Percentile va ranking
SELECT 
    category,
    COUNT(*) as books_count,
    AVG(price) as avg_price,
    MAX(price) as max_price,
    MIN(price) as min_price,
    (MAX(price) - MIN(price)) as price_range
FROM books 
GROUP BY category;

-- Conditional aggregation
SELECT 
    category,
    COUNT(*) as total_books,
    COUNT(CASE WHEN price > 30 THEN 1 END) as expensive_books,
    COUNT(CASE WHEN publication_year > 2020 THEN 1 END) as recent_books
FROM books 
GROUP BY category;

-- Index yaratish aggregation uchun
CREATE INDEX idx_books_category ON books(category);
CREATE INDEX idx_books_author ON books(author);
CREATE INDEX idx_books_price ON books(price);
CREATE INDEX idx_books_year ON books(publication_year);

-- Murakkab index
CREATE INDEX idx_books_category_price ON books(category, price);

-- Performance test
EXPLAIN QUERY PLAN 
SELECT category, COUNT(*) 
FROM books 
GROUP BY category;

-- Natija: SCAN books USING INDEX idx_books_category

-- Xato: GROUP BY da bo'lmagan ustun
SELECT category, author, COUNT(*) 
FROM books 
GROUP BY category;  -- Xato: author GROUP BY da yo'q

-- To'g'ri:
SELECT category, author, COUNT(*) 
FROM books 
GROUP BY category, author;

-- Xato: Aggregation funksiyasi WHERE da
SELECT COUNT(*) 
FROM books 
WHERE COUNT(*) > 1;  -- Xato: WHERE da aggregation ishlatilmaydi

-- To'g'ri:
SELECT category, COUNT(*) 
FROM books 
GROUP BY category 
HAVING COUNT(*) > 1;

-- Blog tizimi analytics
SELECT 
    author,
    COUNT(*) as post_count,
    AVG(view_count) as avg_views,
    SUM(view_count) as total_views,
    MAX(created_at) as last_post
FROM posts 
GROUP BY author 
HAVING COUNT(*) > 1
ORDER BY total_views DESC;

-- E-commerce sales report
SELECT 
    strftime('%Y-%m', order_date) as month,
    COUNT(*) as order_count,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(total_amount) as total_revenue,
    AVG(total_amount) as avg_order_value
FROM orders 
WHERE status = 'completed'
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

-- Product performance
SELECT 
    product_id,
    COUNT(*) as order_count,
    SUM(quantity) as total_quantity,
    SUM(quantity * price) as total_revenue,
    AVG(price) as avg_price
FROM order_items 
GROUP BY product_id 
HAVING COUNT(*) > 5
ORDER BY total_revenue DESC;