Obtain a report using a variety of different conditions

Question

Obtain a report using a variety of different conditions

My database has a table with the following fields:

TPI
CLICKS
IMPRESSION
CLASSIFY

I am looking to retrieve 3 specific results:

1) Calculate SUM(CLICKS)/SUM(IMPRESSION) * 100 GROUPED BY TPI
2) Calculate SUM(IMPRESSION) WHERE CLASSIFY = "XYZ" GROUPED BY TPI
3) Calculate SUM(IMPRESSION) WHERE CLASSIFY = "ABC" GROUPED BY TPI

The desired output format is:

TPI 1st_data 2nd_data 3rd_data

I want the output to only include unique entries for TPI. Is there a way to achieve this using a single query?

Thank you in advance!

javascript mysql sql database

Answer 1

Answer №1

To achieve your desired result, you can utilize conditional aggregation. By incorporating a CASE statement within the argument of sum(), you can selectively include the value of impression based on the condition being met for classify.

SELECT tpi,
       sum(clicks) / sum(impression) * 100 AS first_data,
       sum(CASE classify
             WHEN 'XYZ' THEN impression
             ELSE 0
           END) AS second_data,
       sum(CASE classify
             WHEN 'ABC' THEN impression
             ELSE 0
           END) AS third_data
FROM elbat
GROUP BY tpi;

Answer 2

To achieve your desired result, you can utilize conditional aggregation. By incorporating a CASE statement within the argument of sum(), you can selectively include the value of impression based on the condition being met for classify.

SELECT tpi,
       sum(clicks) / sum(impression) * 100 AS first_data,
       sum(CASE classify
             WHEN 'XYZ' THEN impression
             ELSE 0
           END) AS second_data,
       sum(CASE classify
             WHEN 'ABC' THEN impression
             ELSE 0
           END) AS third_data
FROM elbat
GROUP BY tpi;

Obtain a report using a variety of different conditions

Answer №1

Similar questions

What is the best way to retrieve the Axios response using Express?

Implementing Do Not Track in an express application

Toggle the visibility of a div by clicking on another div in a

Error: Node application using Express does not display 'Server Started' message in console upon execution

Confusion about the unwinding of the call stack in the Graph Depth-

Chargebee encountered an error of type TypeError when attempting to create a subscription. The action

What is the proper way to eliminate the 'unique' attribute from a column in the users table in Laravel 5.3?

Setting a specific time zone as the default in Flatpickr, rather than relying on the system's time zone, can be

What is the best way to target and manipulate the transform property of multiple div elements in JavaScript?

Revise the code to eliminate the need for numerous SQL queries

Access and retrieve pkpass files from a server using React

Do we really need to create our own action creators in Redux Toolkit?

Angular Cascade of Multiple Requests

Exploring the beauty of ASCII art on a webpage

How to display all elements in a Postgreql JSON data array

What is the process for deleting an event in Vue?

"Contrasting the initialization of state in the constructor with managing state

Tips on finding the ID of a textbox using the cursor's position

Ways to verify multiple radio groups to ensure none have been left unchecked

Waiting for the initial render before using document.getElementById() in next.js