Adding a Count Function to an Existing SQL Query for Improved Data Analysis and Insights

Adding a Count Function to an Existing Query

In this article, we will explore how to add a count function to an existing query. We will use SQL as our programming language and examine the query provided by the user.

Understanding the Provided Query

The original query is quite complex, involving multiple joins and conditions. The goal of the query is to retrieve specific data from four tables: GROSS, TARIFF, SERVICE, and SUBSCRIBER. Here’s a breakdown of what the query does:

It selects data from the GROSS table based on certain conditions.
It joins the TARIFF table with GROSS using the SUB_ID column as a common link.
The query then joins the SERVICE table with TARIFF, again using the SUB_ID column.
Next, it joins the SUBSCRIBER table with SERVICE on the same SUB_ID column.
It filters data based on specific conditions such as the month and year of dates in the GROSS table.

Understanding SQL Case Statements

SQL case statements are used to execute different blocks of code depending on certain conditions. The syntax for a case statement is:

SELECT column1, column2, ...
  CASE WHEN condition THEN value1 ELSE value2 END AS alias
FROM table;

In the given query, we want to add a count function that counts how many active users (SUBSCRIBER.STATUS = 1) in general out of all users whose SERVICE.COMPANY_NAME is the same as the one received user. To achieve this, we need to use a SQL case statement.

Modifying the Query

To add the count function, we will modify the query by adding a case statement that checks for the specified conditions and returns 1 if true and 0 otherwise.

Here’s how the modified query looks like:

SELECT GROSS.DATE_ACT, 
       GROSS.SUB_ID, 
       TARIFF.PP_NAME, 
       SERVICE.SERVICE_NAME, 
       SUBSCRIBER.COMPANY_NAME,
       SUM(CASE WHEN(SUBSCRIBER.STATUS = 1 and SUBSCRIBER.COMPANY_NAME = 'MTS') THEN 1 ELSE 0 END) as CNT
FROM GROSS 
INNER JOIN TARIFF ON GROSS.SUB_ID = TARIFF.SUB_ID 
INNER JOIN SERVICE ON TARIFF.SUB_ID = SERVICE.SUB_ID 
INNER JOIN SUBSCRIBER ON SERVICE.SUB_ID = SUBSCRIBER.SUB_ID
WHERE MONTH(GROSS.DATE_ACT) = 12
  AND GROSS.CUSTOMER_TYPE = 'Business'
  AND TARIFF.PP_NAME regexp 'Бизнес-план.+'
  AND GROSS.DATE_ACT = SERVICE.SERVICE_START_DATE
  AND SERVICE.SERVICE_NAME IN ('Безлимитный интернет 512', 'Безлимитный интернет 1', 'Безлиmiteйный интернет 2')
  AND YEAR(SERVICE.SERVICE_STOP_DATE) = 2999
GROUP BY GROSS.DATE_ACT, 
         GROSS.SUB_ID, 
         TARIFF.PP_NAME, 
         SERVICE.SERVICE_NAME, 
         SUBSCRIBER.COMPANY_NAME;

In the modified query, we added a case statement that checks if SUBSCRIBER.STATUS = 1 and SUBSCRIBER.COMPANY_NAME = 'MTS'. If true, it returns 1; otherwise, it returns 0. The SUM function then sums up these values for all rows in the result set.

Final Query

After applying the modifications to the query, we get the final modified query that adds a count function:

SELECT GROSS.DATE_ACT, 
       GROSS.SUB_ID, 
       TARIFF.PP_NAME, 
       SERVICE.SERVICE_NAME, 
       SUBSCRIBER.COMPANY_NAME,
       SUM(CASE WHEN(SUBSCRIBER.STATUS = 1 and SUBSCRIBER.COMPANY_NAME = 'MTS') THEN 1 ELSE 0 END) as CNT
FROM GROSS 
INNER JOIN TARIFF ON GROSS.SUB_ID = TARIFF.SUB_ID 
INNER JOIN SERVICE ON TARIFF.SUB_ID = SERVICE.SUB_ID 
INNER JOIN SUBSCRIBER ON SERVICE.SUB_ID = SUBSCRIBER.SUB_ID
WHERE MONTH(GROSS.DATE_ACT) = 12
  AND GROSS.CUSTOMER_TYPE = 'Business'
  AND TARIFF.PP_NAME regexp 'Бusiness-plan.+'
  AND GROSS.DATE_ACT = SERVICE.SERVICE_START_DATE
  AND SERVICE.SERVICE_NAME IN ('Безлимитный интернет 512', 'Безлиmiteйный интернет 1', 'Безлиmiteйный интернет 2')
  AND YEAR(SERVICE.SERVICE_STOP_DATE) = 2999
GROUP BY GROSS.DATE_ACT, 
         GROSS.SUB_ID, 
         TARIFF.PP_NAME, 
         SERVICE.SERVICE_NAME, 
         SUBSCRIBER.COMPANY_NAME;

Conclusion

In this article, we added a count function to the original query using SQL case statements. The modified query now includes a column that counts how many active users with SUBSCRIBER.COMPANY_NAME equal to ‘MTS’ there are in general out of all users whose SERVICE.COMPANY_NAME is the same as the one received user.

The final answer is:

SELECT GROSS.DATE_ACT, 
       GROSS.SUB_ID, 
       TARIFF.PP_NAME, 
       SERVICE.SERVICE_NAME, 
       SUBSCRIBER.COMPANY_NAME,
       SUM(CASE WHEN(SUBSCRIBER.STATUS = 1 and SUBSCRIBER.COMPANY_NAME = 'MTS') THEN 1 ELSE 0 END) as CNT
FROM GROSS 
INNER JOIN TARIFF ON GROSS.SUB_ID = TARIFF.SUB_ID 
INNER JOIN SERVICE ON TARIFF.SUB_ID = SERVICE.SUB_ID 
INNER JOIN SUBSCRIBER ON SERVICE.SUB_ID = SUBSCRIBER.SUB_ID
WHERE MONTH(GROSS.DATE_ACT) = 12
  AND GROSS.CUSTOMER_TYPE = 'Business'
  AND TARIFF.PP_NAME regexp 'Бusiness-plan.+'
  AND GROSS.DATEAkt = SERVICE.SERVICE_START_DATE
  AND SERVICE.SERVICE_NAME IN ('Безлимитный интернет 512', 'Безлиmiteйный интернет 1', 'Безлиmiteйный интернет 2')
  AND YEAR(SERVICE.SERVICE_STOP_DATE) = 2999
GROUP BY GROSS.DATE_ACT, 
         GROSS.SUB_ID, 
         TARIFF.PP_NAME, 
         SERVICE.SERVICE_NAME, 
         SUBSCRIBER.COMPANY_NAME;

Last modified on 2025-03-11