Analyze Supermarket Sales with SQL

Query a real sales dataset in SQL Server and build a Power BI dashboard.

Introduction

30 Second Summary

Every supermarket generates thousands of transactions daily. Turning that raw data into actionable business insights is exactly what data analysts do.

In this project, you will import a real supermarket sales dataset into SQL Server, write queries to uncover revenue trends, and build an interactive Power BI dashboard to visualize your findings.

What You'll Build

A complete data analysis workflow that takes 1,000 real supermarket transactions from a CSV file through SQL analysis to an interactive dashboard.

By the end of this project, you'll have:

  • A SQL Server database loaded with 1,000 real supermarket transactions ready for analysis.
  • A set of SQL queries answering business questions like "which product line generates the most revenue?" and "what are the sales trends by month?".
  • An interactive Power BI dashboard with charts showing revenue by product line, sales trends over time, and branch performance comparisons.
  • Secret Mission: customer segmentation analysis comparing Member vs Normal customers and their spending patterns.

Are there any prerequisites?

This project assumes you have SQL Server Management Studio (SSMS) and Power BI Desktop already installed on a Windows device. Both tools are free. The guide includes verification steps so you can confirm everything is working before you begin.

Set Up Your Database and Import Data

Every data analysis project starts with data. Before you can write queries or build dashboards, you need to get your dataset into a database where SQL Server can work with it.

In this step, you'll download a real supermarket sales dataset containing 1,000 transactions across 3 store branches. Then you'll create a fresh database in SSMS and import the data using a built-in wizard.

In this step, get ready to:
  • Download a real-world supermarket sales dataset (1,000 transactions).
  • Create a new SQL Server database named SupermarketSales.
  • Import the CSV data into a Sales table and verify it loaded correctly.
Download the dataset and create your database

The dataset you'll use contains real point-of-sale records from three supermarket branches. It includes information like product lines, payment methods, and customer types across January to March 2019.

What is this dataset?

The Supermarket Sales dataset has 1,000 rows and 17 columns. Each row represents one customer transaction at one of three store branches (A, B, or C).

Columns include Invoice ID, Branch, Product line, Total, Date, and more. You'll explore all of these in your SQL queries later.

Now you'll create the database that will hold this data.

  • Switch back to SSMS.
  • In the Object Explorer panel on the left, right-click on Databases.
  • Select New Database... from the context menu.
  • In the Database name field, type SupermarketSales.
  • Click OK.

You should now see SupermarketSales listed under the Databases folder in Object Explorer. If you don't see it, right-click Databases and select Refresh.

Don't see the New Database option?

Make sure you're right-clicking on the Databases folder itself, not on an individual database or the server name. Also confirm that your connection to the local server is still active (the icon should not show a red X).

help me connect to my local SQL Server in SSMS

Import the CSV using the Import Flat File wizard

SSMS has a built-in wizard that reads your CSV file, detects the column structure, and creates a table for you automatically. This saves you from writing a CREATE TABLE statement with all 17 columns by hand.

  • In Object Explorer, expand Databases, then right-click on SupermarketSales.
  • Select Tasks, then click Import Flat File....
  • On the Introduction page, click Next.
  • Click Browse and navigate to the supermarket_sales.csv file on your Desktop.
  • In the New table name field, type Sales.
  • Click Next.

The wizard now shows a preview of the first 50 rows so you can confirm the data looks correct.

  • Review the preview to confirm you see columns like Invoice ID, Branch, City, and Product line.
  • Click Next to move to the Modify Columns step.
  • Review the detected column names and data types. Ensure Allow Nulls is checked for columns where it makes sense.
  • Click Next to proceed to the summary page.
  • Click Finish to start the import.

You should see a green check mark next to each step, indicating the import completed successfully.

What did the Import Flat File wizard do?

The wizard used PROSE (Program Synthesis using Examples) to automatically detect the CSV delimiter, column names, and appropriate data types. It then created the Sales table inside your SupermarketSales database and inserted all 1,000 rows.

This approach avoids the older "Import Data" option which requires Integration Services (SSIS) components that may not be installed with SSMS 22.

Seeing an error during import?

If you get a message about the table name already existing, it means a table called Sales was already created in a previous attempt. Change the table name to something unique, or delete the existing table first by expanding Tables under your database, right-clicking the old table, and selecting Delete.

If Import Flat File doesn't appear in the Tasks menu, make sure you right-clicked on the SupermarketSales database specifically, not on the server name or the Databases folder.

help me troubleshoot the Import Flat File wizard in SSMS

Verify your data import

Now you'll run two quick queries to confirm the data landed correctly in your database.

  • Right-click on SupermarketSales in Object Explorer and select New Query.

This opens a blank query editor connected to your database.

  • Check the total row count by running this query:
-- Count total rows to confirm all 1,000 transactions imported
SELECT COUNT(*) AS TotalRows FROM Sales;
  • Click Execute in the toolbar (or press F5).

You should see a result of 1000 in the TotalRows column.

  • Clear the query editor and run this next query to inspect a few rows:
-- Preview the first 5 rows to check column structure
SELECT TOP 5 * FROM Sales;
  • Click Execute (or press F5).

You should see 5 rows with columns including Invoice ID, Branch, City, Product line, Unit price, Quantity, Total, and Date.

Getting 0 rows or an error?

If TotalRows returns 0, the import may have failed silently. Go back and redo the Import Flat File wizard, making sure you select the correct CSV file and that the wizard shows the green check marks at the end.

If you see "Invalid object name 'Sales'", confirm the query window is connected to the SupermarketSales database. Check the database dropdown at the top of the query editor toolbar. It should show SupermarketSales, not master.

help me fix my SQL query that's not returning data from my Sales table

Your database is loaded with 1,000 real transactions and ready for analysis. Next up, you'll write SQL queries to uncover business insights hidden in this data.

Write SQL Queries to Answer Business Questions

Your SupermarketSales database is loaded with 1,000 transactions. The data is sitting there, but raw rows don't tell a story on their own.

In this step, you will write SQL queries that answer real business questions. Which product line brings in the most money? Are sales growing or shrinking? Which branch is the top performer? Each query transforms raw data into a clear insight.

In this step, get ready to:
  • Calculate total revenue by product line using GROUP BY and SUM.
  • Analyze monthly sales trends using date functions.
  • Compare branch performance with multiple aggregate functions.
Calculate total revenue by product line

A store manager's first question is usually: which products make us the most money? You can answer this by grouping all transactions by their product line and summing up the revenue for each.

  • In the Query Editor window from the previous step (already connected to SupermarketSales), clear any existing queries and paste the following:
-- Total revenue by product line, ranked highest to lowest
SELECT [Product line], SUM(Total) AS TotalRevenue
FROM Sales
GROUP BY [Product line]
ORDER BY TotalRevenue DESC;
  • Run the query by pressing F5 or clicking the Execute button in the toolbar.

What does this query do?

  • GROUP BY [Product line] collects all rows with the same product line into a single group.
  • SUM(Total) adds up the Total column for every row in each group, giving you the total revenue per product line.
  • ORDER BY TotalRevenue DESC sorts the results from highest revenue to lowest, so the top earner appears first.

You should see 6 rows in your results grid, one for each product line. The product lines include Food and beverages, Fashion accessories, Electronic accessories, Sports and travel, Home and lifestyle, and Health and beauty.

Seeing an error about the column name?

Column names with spaces (like Product line) must be wrapped in square brackets [Product line] in SQL Server. Make sure you have the brackets around the column name.

Also confirm the database dropdown at the top of the Query Editor still shows SupermarketSales. If it shows master, change it back.

help me fix my GROUP BY query

Analyze monthly sales trends

Knowing how sales change over time helps businesses plan inventory and staffing. The dataset covers January through March 2019, so you can check whether sales grew, declined, or stayed flat across those three months.

  • Add a blank line below your first query and paste this second query:
-- Monthly sales trend for Jan-Mar 2019
SELECT MONTH(Date) AS Month, SUM(Total) AS MonthlySales
FROM Sales
GROUP BY MONTH(Date)
ORDER BY Month;
  • Highlight this query (select from SELECT to the semicolon) and press F5 to run just this one.

What does this query do?

MONTH(Date) extracts the month number (1, 2, or 3) from the Date column. Grouping by this value collapses all January transactions into one row, all February into another, and all March into a third.

The result shows you whether total sales increased or decreased month over month across the three-month period.

You should see 3 rows in the results. Each row shows a month number (1, 2, or 3) and its total sales figure.

Seeing NULL or unexpected values in the Month column?

If the Date column was imported as text instead of a date type, MONTH() may not work correctly. To check, run SELECT TOP 5 Date FROM Sales; and look at the format. If dates appear as plain text, you may need to cast them first.

help me fix my Date column issue

Compare branch performance and write additional queries

The dataset includes 3 branches (A, B, and C) located in different cities. Comparing their total revenue, average transaction size, and transaction count in a single query gives you a complete performance picture.

  • Add a blank line below your second query and paste the branch comparison query:
-- Branch performance comparison
SELECT Branch, SUM(Total) AS TotalRevenue, AVG(Total) AS AvgTransaction, COUNT(*) AS TransactionCount
FROM Sales
GROUP BY Branch
ORDER BY TotalRevenue DESC;
  • Highlight and run this query with F5.

What does this query do?

This query uses three aggregate functions in one go. SUM(Total) calculates the total revenue for each branch. AVG(Total) calculates the average transaction value. COUNT(*) counts how many transactions each branch processed.

Together, these three numbers reveal whether a branch earns more because of higher-value purchases or simply more customers walking in.

You should see 3 rows, one per branch (A, B, C), each with three metrics. Identify which branch has the highest total revenue and which has the highest average transaction.

Results not showing 3 rows?

Make sure you highlighted only the branch comparison query before pressing F5. If you run the entire script, SSMS executes all queries and shows multiple result tabs.

help me run a single query in SSMS

Now add two more queries to round out your analysis. These explore payment methods and customer satisfaction.

  • Add the following payment method breakdown query below your branch comparison:
-- Payment method popularity and revenue
SELECT Payment, COUNT(*) AS TransactionCount, SUM(Total) AS TotalRevenue
FROM Sales
GROUP BY Payment
ORDER BY TotalRevenue DESC;

What does this query do?

This groups transactions by payment type (Ewallet, Cash, or Credit card) and shows both how often each is used and how much revenue it generates. This helps the business understand customer payment preferences.

  • Add one final query to check average customer ratings by product line:
-- Average customer rating by product line
SELECT [Product line], AVG(Rating) AS AvgRating
FROM Sales
GROUP BY [Product line]
ORDER BY AvgRating DESC;

What does this query do?

AVG(Rating) calculates the mean customer satisfaction score for each product line. Product lines with high revenue but low ratings might need attention. Product lines with high ratings but low revenue might have untapped marketing potential.

  • Highlight and run each new query individually with F5 to verify results.
  • Save your query script by pressing Ctrl+S. Name the file SupermarketAnalysis.sql and save it to your Desktop.

You now have 5 analytical queries saved in a single script. The payment method query should show 3 rows (Ewallet, Cash, Credit card). The rating query should show 6 rows (one per product line).

SSMS asking where to save?

When you press Ctrl+S on a new query window, SSMS opens a Save As dialog. Navigate to your Desktop, type SupermarketAnalysis as the filename, and click Save. The file will be saved with a .sql extension.

help me save my SQL queries

✔️ Awesome, I've got everything!

Great. Make sure you have saved your SupermarketAnalysis.sql file. You will connect to this data in Power BI next.

ⓧ I'd like to double check the full code

Here is the complete SQL script with all 5 queries:

-- Total revenue by product line, ranked highest to lowest
SELECT [Product line], SUM(Total) AS TotalRevenue
FROM Sales
GROUP BY [Product line]
ORDER BY TotalRevenue DESC;

-- Monthly sales trend for Jan-Mar 2019
SELECT MONTH(Date) AS Month, SUM(Total) AS MonthlySales
FROM Sales
GROUP BY MONTH(Date)
ORDER BY Month;

-- Branch performance comparison
SELECT Branch, SUM(Total) AS TotalRevenue, AVG(Total) AS AvgTransaction, COUNT(*) AS TransactionCount
FROM Sales
GROUP BY Branch
ORDER BY TotalRevenue DESC;

-- Payment method popularity and revenue
SELECT Payment, COUNT(*) AS TransactionCount, SUM(Total) AS TotalRevenue
FROM Sales
GROUP BY Payment
ORDER BY TotalRevenue DESC;

-- Average customer rating by product line
SELECT [Product line], AVG(Rating) AS AvgRating
FROM Sales
GROUP BY [Product line]
ORDER BY AvgRating DESC;

You now have a complete set of analytical queries that answer real business questions. Next, you will connect Power BI to your database and turn these insights into interactive visualizations.

Connect Power BI to SQL Server

Your SQL queries uncovered insights about product performance, sales trends, and branch comparisons. Now it's time to turn those numbers into visual stories.

Power BI Desktop connects directly to your SQL Server database and pulls in the raw data. From there, you can build interactive charts that make patterns obvious at a glance.

In this step, get ready to:
  • Connect Power BI Desktop to your local SQL Server instance.
  • Load the Sales table into Power BI for visualization.
Open Power BI Desktop and connect to your database

Power BI needs a live connection to your SQL Server database so it can pull in the Sales table data directly. You will use the built-in SQL Server connector for this.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.

Power BI Desktop may show a splash screen or sign-in prompt when it first opens. You can close it or skip the sign-in for now.

  • On the Home tab in the ribbon, click Get Data.
  • Select SQL Server from the dropdown.

A dialog box appears asking for your server and database details.

  • In the Server field, enter localhost (this matches what you used in SSMS).
  • In the Database field, enter SupermarketSales.
  • Set Data Connectivity mode to Import.
  • Click OK.

What is Import mode?

Import mode copies all the data from SQL Server into Power BI's in-memory engine. This makes your charts load instantly because Power BI doesn't need to query the database every time you interact with a visual.

The alternative, DirectQuery, queries the database live on every interaction. For a small dataset like 1,000 rows, Import is faster and simpler.

  • Power BI prompts for authentication. Select Windows (this should already be selected by default).
  • Click Connect.

Connection failed?

Make sure the Server field matches exactly what you used to connect in SSMS. If localhost doesn't work, try your machine name instead.

Also confirm that your SQL Server instance is still running. Switch back to SSMS and check that you can still run a query against the SupermarketSales database.

Help me troubleshoot my Power BI connection to SQL Server

Load the Sales table

After connecting, Power BI shows a Navigator dialog listing all available tables in your database. You need to select which table to import.

  • In the Navigator dialog, check the box next to the Sales table.
  • A preview of your data appears on the right side of the dialog.
  • Click Load to import the data into Power BI.

Power BI pulls in all 1,000 rows. A loading indicator appears briefly while the import completes.

  • Click the Data view icon (the table icon) on the left sidebar to switch to Data view.
  • Confirm that you see all 1,000 rows displayed in the table.
  • Check the Fields pane on the right. You should see all 17 columns listed under the Sales table, including Invoice ID, Branch, Product line, Total, and Date.

What should I see?

Your Data view should display a spreadsheet-like grid with all your supermarket sales records. The Fields pane on the right lists every column name from the Sales table.

If you see fewer than 17 columns or the data looks empty, try deleting this data source and re-importing using the same steps above.

Don't see 1,000 rows?

Power BI's Data view may not display all 1,000 rows at once in the grid, but the status bar at the bottom confirms the full row count. If the table appears empty, make sure you clicked Load (not Transform Data) in the Navigator dialog.

Help me figure out why my Sales table didn't load correctly in Power BI

Your data is now inside Power BI and ready to visualize. Next up, you'll build an interactive dashboard with charts that reveal sales patterns across product lines, time, and branches.

Build Your Sales Dashboard

Your SQL queries revealed which product lines drive the most revenue, how sales trend across months, and which branches outperform others. Now it's time to turn those insights into something visual and interactive.

In this step, you'll build three charts in Power BI Desktop that answer the same business questions your SQL explored. The best part: Power BI automatically cross-filters your charts, so clicking on one visual updates the others.

In this step, get ready to:
  • Create a stacked bar chart showing revenue by product line.
  • Create a line chart showing monthly sales trends.
  • Create a donut chart comparing branch performance.
Create a revenue by product line chart

The first chart answers your biggest business question: which product lines generate the most revenue? A stacked bar chart makes this comparison easy to read at a glance.

  • In Power BI Desktop, switch to Report view by clicking the chart icon on the left sidebar.
  • In the Visualizations pane on the right, click the Stacked bar chart icon.
  • From the Fields pane, drag Product line to the Y-axis field well.
  • Drag Total to the X-axis field well.

You should see a horizontal bar chart with 6 bars, one for each product line. The longest bar represents the highest-revenue product line.

What do the bars represent?

Each bar shows the sum of the Total column for all transactions in that product line. Power BI automatically aggregates the values using SUM when you drag a numeric field into a Values or Axis well.

Don't see 6 bars?

Make sure you dragged Product line to the Y-axis (not the X-axis). The Y-axis controls the categories in a stacked bar chart.

If the chart is blank, confirm you selected the Stacked bar chart icon (horizontal bars) and not the Stacked column chart (vertical bars). Both work, but the outline uses the bar chart for readability.

Help me troubleshoot my Power BI bar chart.

Create a monthly sales trend line chart

Your second chart shows how total sales change over time. A line chart is ideal for spotting trends across the January to March 2019 period in this dataset.

  • Click on an empty area of the canvas to deselect the bar chart.
  • In the Visualizations pane, click the Line chart icon.
  • Drag Date to the X-axis field well.
  • Drag Total to the Y-axis field well.

Why does the chart show Year instead of months?

Power BI automatically creates a date hierarchy (Year, Quarter, Month, Day) when it detects a date column. Your chart initially displays data at the Year level. You need to drill down to reach the Month level.

  • With the line chart selected, look for the drill icons at the top of the visual. Click the Go to the next level in the hierarchy icon (a double downward arrow). This moves the chart down one level in the date hierarchy.
  • Click the same icon again if needed until the X-axis shows individual months (January, February, March).

You should see a line chart with data points for each month, showing how total sales changed from January to March 2019.

Drill icons not visible?

Hover over the line chart to reveal the drill icons. They appear at the top-left or bottom-right corner of the visual depending on its position on the canvas.

Chart shows a single data point?

If you see only one point at the Year level, make sure you clicked the Go to the next level in the hierarchy icon (double downward arrow), not the Drill down icon (single downward arrow). The single arrow requires you to click a specific data point first.

Help me drill down to month level in my Power BI line chart.

Create a branch comparison donut chart

Your final chart compares how much revenue each branch contributes to the total. A donut chart makes it easy to see each branch's share at a glance.

  • Click on an empty area of the canvas to deselect the line chart.
  • In the Visualizations pane, click the Donut chart icon.
  • Drag Branch to the Legend field well.
  • Drag Total to the Values field well.

You should see a donut chart split into three segments representing Branches A, B, and C.

What does cross-filtering mean?

Power BI automatically connects all visuals on the same page. When you click a bar in the product line chart, the line chart and donut chart update to show data for that product line only. This is called cross-filtering.

Try clicking one of the product line bars now. You'll see the other two charts respond instantly.

  • Save your report by pressing Ctrl+S.
  • Name the file SupermarketSales and save it to a location you can find easily (like your Desktop).

Your completed dashboard now has 3 interactive visuals on a single page. You can see which products sell best, how sales trend over time, and how branches compare.

Donut chart only shows one segment?

Make sure you dragged Branch to the Legend field well (not Values). The Legend field controls how the donut splits into segments.

Help me fix my Power BI donut chart.

Secret mission

Customer Segmentation Analysis

Your dashboard shows overall sales performance, but the business wants to know if their loyalty program is working. Add a customer segmentation layer that compares Member vs Normal customers and reveals whether Members actually spend more.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources on your machine, or delete them entirely.

Resources you used:

  • SupermarketSales database on your local SQL Server instance (no cost, local storage only).
  • SupermarketSales.pbix Power BI report file (local file, no cost).
  • supermarket_sales.csv file downloaded to your Desktop.

Keep everything running

No action needed. Choose this if you want to keep exploring the data, adding more queries, or building additional Power BI visuals.

  • Your SupermarketSales database stays on your local SQL Server at no cost.
  • Your SupermarketSales.pbix file stays wherever you saved it.
  • The CSV file remains on your Desktop for future reference.

Pause - I'll come back to this later

Since everything is local and there are no running services or costs, there is nothing to pause. Your database and files stay on your machine until you choose to delete them.

  • Close SSMS and Power BI Desktop to free up memory.
  • Your database and files will be exactly as you left them next time you open the tools.

Delete - I don't want to use this again

Remove all project resources from your machine. This cannot be undone.

  • Delete the database: in SSMS, expand Databases in Object Explorer, right-click SupermarketSales, and select Delete. Check Close existing connections and click OK.
  • Delete the Power BI file: navigate to where you saved SupermarketSales.pbix in File Explorer and delete it.
  • Delete the CSV file: navigate to your Desktop in File Explorer and delete supermarket_sales.csv.

Nice Work!

Nice Work!

You just built a complete data analysis workflow from scratch. You took a raw CSV of 1,000 supermarket transactions, loaded it into a database, queried it for real business insights, and presented your findings in an interactive dashboard.

You've learned how to:

  • Import a real-world dataset into SQL Server using the Import Flat File wizard and verify the data with queries.
  • Write analytical SQL queries using GROUP BY, aggregate functions, and date functions to answer business questions about product performance, sales trends, and branch comparisons.
  • Build an interactive Power BI dashboard with cross-filtering visualizations connected to your local SQL Server database.
  • Secret Mission: Add customer segmentation analysis with slicers and comparative charts to filter your dashboard by Member vs Normal customers.

Ready to quiz yourself?