Day: August 18, 2024

library(RSQLite)
library(DBI)
library(ggplot2)

# Define the path to your SQLite database
db_path <- "/home/jbyungrokim/CSV/CSV.db"

# Connect to the SQLite database
conn <- dbConnect(RSQLite::SQLite(), dbname = db_path)

# Corrected query to get the total spending by each item for July 2024, excluding 'Rent - Monthly' and 'Grocery'
query <- "
SELECT 
    Item, 
    SUM(Pay_Amount) AS Total_Spent
FROM 
    '지출-액수_2024_08_17'
WHERE 
    substr(Date_Transaction, 1, 2) = '07' 
    AND substr(Date_Transaction, 7, 2) = '24'
GROUP BY 
    Item
ORDER BY 
    Total_Spent DESC;
"

# Execute the query and store the result in a data frame
july_spending <- dbGetQuery(conn, query)

# Close the connection to the SQLite database
dbDisconnect(conn)

# Create a bar graph using ggplot2
ggplot(july_spending, aes(x = reorder(Item, -Total_Spent), y = Total_Spent, fill = Item)) +
  geom_bar(stat = "identity") +
  labs(title = "Spending Breakdown by Item - July 2024 (Excluding Rent and Grocery)",
       x = "Item",
       y = "Total Spent") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1),
        legend.position = "none")

library(RSQLite)
library(DBI)

# Define the path to your SQLite database
db_path <- "/home/jbyungrokim/CSV/CSV.db"

# Connect to the SQLite database
conn <- dbConnect(RSQLite::SQLite(), dbname = db_path)

# Adjust the query to calculate the sum for each item in July 2024
query <- "
SELECT 
    Item, 
    SUM(Pay_Amount) AS Total_Spent
FROM 
    '지출-액수_2024_08_17'
WHERE 
    strftime('%Y-%m', '20' || substr(Date_Transaction, 7, 2) || '-' || substr(Date_Transaction, 1, 2) || '-' || substr(Date_Transaction, 4, 2)) = '2024-03'
GROUP BY 
    Item
ORDER BY 
    Total_Spent DESC;
"

# Execute the query and store the result in a data frame
july_2024_sum <- dbGetQuery(conn, query)

# Print the result
print("Sum of Pay_Amount by Item for March 2024:")
print(july_2024_sum)

# Close the connection to the SQLite database
dbDisconnect(conn)