DEV Community: Solomon

WHERE vs HAVING in SQL: A Simple Way to Understand the Difference

Solomon — Mon, 12 Oct 2026 11:30:10 +0000

If you’ve ever mixed up WHERE and HAVING, you’re not alone. They both filter data, so it feels like you should be able to use either one. But they filter at different moments, and once you get that, the confusion goes away.

The one-line answer
WHERE filters individual rows before any grouping happens.
HAVING filters groups after the grouping and math (SUM, COUNT, AVG) is done.

A Quick Analogy
Suppose there is an event in which the individuals attending are divided into tables:
WHERE is the bouncer at the door. Allows entry on a first-come, first-served basis, according to the individual person profile, i.e ('no under-18s').
HAVING is the host verifying the tables after everyone has been seated. If a table has less than 4 people, it is merged with another table. The host can only evaluate a table after it is created.

The bouncer can't tell if the table is good or bad, since there are no tables at the door. Because of this, aggregate functions such as SUM() and COUNT() cannot be used in WHERE.
We will use a small orders table to make it easy to verify the results in pgAdmin.

Step 1: WHERE filters rows
If we just want orders worth more than 100:

SELECT * FROM orders
WHERE amount > 100;

You'll get 4 rows (500, 300, 120, 700). Postgres iterated through each row and asked, "Is this over 100?" Simple.

Step 2: GROUP BY

Now, let's take a look at the total amount spent by each customer so we have something to filter later.

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer;

You should see:

Step 3: HAVING filters groups
What if we just want the customers who spent more than 200 in total?

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
HAVING SUM(amount) > 200;

Chloe (180) disappears. In this case, we are filtering the totals, not the rows.

Step 4: Using both together
This is where it comes home to me. Let's say we want the total money spent per customer, but only those that have completed orders and only those whose total money spent is greater than 200:

SELECT customer, SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer
HAVING SUM(amount) > 200;

Results:

Notice what changed compared to Step 3. David is gone, because his big 700 order was cancelled, so WHERE threw it out before the math happened. His completed total is only 60. Brian’s total also dropped from 380 to 300 because his cancelled order was removed.

Why the order matters

SQL doesn’t run your query in the order you write it. It runs roughly like this:

FROM (pick the table)
WHERE (filter rows)
GROUP BY (make the groups)
HAVING (filter groups)
SELECT (pick the columns)
ORDER BY (sort)

WHERE comes before GROUP BY, so it can only see raw rows. HAVING comes after, so it can see the group totals. That one fact explains everything.

From Managed Cloud DB to Dashboards: Connecting Aiven PostgreSQL with DBeaver, pgAdmin, and Power BI

Solomon — Tue, 21 Jul 2026 11:01:07 +0000

In this article, I would like to walk you through the journey of storing data, querying data, and visualizing data in an organised and safe manner.
Starting with Aiven, which is a cloud data warehouse host; later on in the article, we will discuss why Aiven.

Secondly, there are DBeaver & pgAdmin; basically, these are command centers for managing, cleaning, and querying the data. The main difference between the two is that PgAdmin only supports PostgreSQL, while DBeaver supports multiple databases.

Lastly, Power BI is a powerful visualization tool used to display a range of visualizations to end users to support data-driven decision-making.

You might ask, "Why connect all these tools?"
The answer is simple: automation and synchronization. By bridging this stack, any changes made to the cloud database can be instantly pushed to the end users. A single click of the "Refresh" button in Power BI automatically updates all the visualizations with real-time data

SETTING UP AIVEN AND WHY AIVEN
The why
Aiven is a completely managed database as opposed to traditional Cloud Providers like AWS or Google Cloud, where you are trapped under their rules and their fluctuating utility bills.
I would want you to think of it like this:
You are a businessperson who wants to deliver goods in the most cost-effective and efficient way
What do you do?
You hire a world-class logistics company instead of buying your own delivery trucks; it gives you the same power to move your goods, but someone else handles all the maintenance, flat tires, and breakdowns for a predictable flat fee.

Basically, that's what Aiven does, but now with data and not goods.

The Setup

Sign Up: Create an account on the Aiven Console.
Deploy the Service: Click Create Project and then click Create Service, select PostgreSQL, and choose your underlying cloud provider and region.
Launch: Pick your plan (in my case, I picked the free tier) and click deploy. Within minutes, your status will change to Running, as shown below

Connecting DBeaver to Aiven
Now that our cloud database is running, we need a command center to talk to it. Let’s start with DBeaver. Because DBeaver is a universal database manager, it handles the connection drivers for us automatically.

What do you do?
Open DBeaver and click on the Plug icon (New Database Connection) in the top left corner.

Select PostgreSQL from the list and click Next.
In the Main tab, fill in your connection details copied straight from your Aiven Console:
Host: Your Aiven host _
URL Port: _5432
Database: defaultdb
Username: avnadmin
Password: "Your secure Aiven password"

Click on the SSL tab at the top. Check the box for Use SSL. Next to Root certificate, browse your computer and select the ca.pem file you downloaded from Aiven. Click Test Connection.

DBeaver will download the drivers, and you will see a success message. Click Finish.

Connecting PGAdmin to Aiven

Next up is pgAdmin. Remember, pgAdmin is specifically built only for PostgreSQL, so its connection settings are tailored a bit differently.

What do you do?

Open pgAdmin, right-click on Servers in the left sidebar, select Register, and click Server.
In the General tab, give your connection a name.
Move to the Connection tab and enter your Aiven details into the Host, Port, Maintenance Database defaultdb, Username, and Password fields.
Move to the Parameters tab. Set the SSL mode dropdown to Require. Click the folder icon and upload your Aiven ca.pem file.
Click Save. Your Aiven database will appear in the sidebar, ready for queries.

Hooking up Power BI to Aiven
Our database is managed and accessible; now it is time to bring the data to life for our end users.

What do you do?

Open Power BI Desktop, go to the Home ribbon, click Get Data, and select PostgreSQL database.
In the Server box, paste your Aiven Host string followed by a colon and the port (e.g., ://aivencloud.com). In the Database box, type defaultdb.
Choose your Data Connectivity Mode:
- Import: Copies a snapshot of the data into Power BI (faster for performance).
- DirectQuery: Queries Aiven live every time someone clicks a visual.
Click OK. A window will pop up asking for credentials. Select Database on the left menu, enter your avnadmin username and password, and click Connect.
The Navigator window will open. Check the boxes next to the tables you want to visualize, click Load, and start dragging fields onto your canvas to build your dashboard!

Conclusion
By connecting Aiven, DBeaver, pgAdmin, and Power BI, you create a seamless pipeline. You can manage your backend structures universally in DBeaver, do deeper Postgres administration in pgAdmin, and serve fresh data to business decision-makers in Power BI with a single click of the refresh button.

Excel Beyond the Cells

Solomon — Sat, 04 Jul 2026 22:08:27 +0000

What comes to mind when you think of "Excel beyond the cells"? Throughout my data analysis journey, one tool that has been full of surprises is Excel. Most of us get to a point where we master the layout, learn several functions, and feel unstoppable. But then you are presented with a workbook containing some nested XLOOKUPs that throw an error at the slightest inconvenience. That is exactly when things get ugly.

1. TAMING THE FORMULAS
Let's start with formulas, the foundation of almost every workbook. When formulas are left untamed, the result is unwanted errors in the workbook. While XLOOKUPs are better compared to old VLOOKUPs due to their ability to check left and right, they become difficult to deal with when left nested blindly. To avoid errors in such formulas, the best practice is to use the IFERROR safety net.

A visual comparison of XLOOKUPs and VLOOKUPs

Example of an XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array,"Not Found")

Natively, XLOOKUP has its own way of dealing with an error using the fourth argument written as "Not Found" in the example. The argument makes sure that the formula returns a value instead of #NA.
When a formula is expected to search multiple sources, IFERROR can be useful for gracefully falling back to another lookup. However, it should be used carefully because it can also hide unexpected errors.

Example of a nested XLOOKUP utilising an IFERROR safety net
=IFERROR(XLOOKUP(A2, Table1_Lookup, Table1_Return), XLOOKUP(A2, Table2_Lookup, Table2_Return))

Visual representation

2. POWER QUERY
Once formulas become difficult to manage, the next logical step is automation with Power Query. Power Query is a robotic tool in Excel that eliminates the need to transform data manually. From deleting blank rows to adding custom columns and splitting columns, Power Query is the right tool for any data cleaning task. The most practical and enticing part is the fact that all these steps are recorded. When new data comes in, the data is cleaned with a single click of the refresh button.
Visual representation

3. Pivot tables (The storyteller)
A pivot table takes thousands of rows and condenses them into a clickable summary. Analysis that can take hours to complete using formulas is completed in seconds using pivot tables. They are vital in creating summary tables and slicers in Excel dashboards, this creates a fully interactive and usable dashboard that can effectively support decision-making.

Combining this and Power Query, a developer can create a self-sustaining ecosystem where the data is refreshed and fully updated on the dashboard

In conclusion, Excel remains one of the best tools for exploring and understanding data. From basic analysis that can be handled using basic formulas to complex analysis that needs Power Query and Power Pivot, Excel is the go-to tool for entry-level analysts. However, as datasets grow, analysts need to use specific tools like Power BI for visualization, which is something I will explore more in my next article.

How Excel is Used in Real-World Data Analysis

Solomon — Sun, 07 Jun 2026 10:45:25 +0000

Introduction
Excel is a versatile software by Microsoft used to explore and manipulate data to generate meaningful insights from data. Due to its versatility, Excel is used in different fields, ranging from finance, engineering, and business
Uses of Excel in real_world data analysis
Excel is used in the field of Health and Medicine to track patient data management systems. This is done by tracking clinic wait times, appointment volumes, and patient demographics. Secondly, researchers use Excel to organize trial phases and patient dosage responses.

On the other hand, Excel is widely used in the field of Finance and Accounting. Billing and payroll heavily depend on Excel to generate automated invoices and expense logs. Also, it is used to track business and personal expenses with tools like a 'Budget tracker' in Excel. This feature helps individuals and businesses operate within their budgets. As a frequent user of a budget tracker, I can confidently say that Excel is a very functional tool in managing finances. Using features like what if analyses helps businesses run financial models and make strategic business decisions.

In Administration and Organization, Excel is used as light weight Customer relationship management(CRM). This is due to its ability to sort, filter, and store customer information. When it comes to task automation, organizations utilize features like VBA and macros to turn repetitive manual processes into automated processes.
Features and Formulas in Excel
The first cool feature I learnt in Excel was the 'fill handle', clicking the tiny green square at the bottom of a cell and dragging it down quickly copies data or continues a pattern. This is one of the easiest and most convenient features in Excel.

Formulas in Excel make it simple to perform basic statistics in Excel. Basic formulas like SUM add a range of numbers together, and COUNT counts how many cells in a range contain numbers.

Personal Reflection
Learning Excel has changed the way I view data. Instead of seeing data as just numbers, I now see it as a source of valuable insights. Through formulas, charts, and analytical tools, I have learnt how to identify trends and support decision-making. As someone interested in data analysis, Excel has become an essential tool for working with data efficiently and effectively.