Performing Multiple Count Operations in MySQL

Database Connection Setup

import mysql.connector

db_connection = mysql.connector.connect(
  host="db_server",
  user="db_user",
  password="secure_password",
  database="inventory_db"
)

Executing Count Queries

db_cursor = db_connection.cursor()

# Count products in different categories
query = """
SELECT 
    COUNT(CASE WHEN category = 'electronics' THEN 1 END) AS electronics_count,
    COUNT(CASE WHEN category = 'clothing' THEN 1 END) AS clothing_count,
    COUNT(CASE WHEN price > 100 THEN 1 END) AS premium_items
FROM products;
"""

db_cursor.execute(query)
count_results = db_cursor.fetchone()

Processing Query Results

electronics, apparel, high_value = count_results
print(f"Electronics: {electronics}")
print(f"Clothing items: {apparel}")
print(f"Premium products: {high_value}")

Resourec Cleanup

db_cursor.close()
db_connection.close()

Alternative Approach Using Multiple Queries

# For separate count queries
db_cursor.execute("SELECT COUNT(*) FROM products WHERE stock < 10")
low_stock = db_cursor.fetchone()[0]

db_cursor.execute("SELECT COUNT(*) FROM products WHERE added_date > '2023-01-01'")
new_products = db_cursor.fetchone()[0]