CODEWITHSUNDEEP
sql
Azizul H
Azizul H

Reputation: 111

SQL Query to Calculate Daily Percentage

I have this data in my table:

date     time     validity
---------------------------
Day1     07:00     valid
Day1     15:00     valid
Day1     23:00     invalid
Day2     07:00     valid
Day2     15:00     valid
Day2     23:00     valid
Day3     07:00     invalid
Day3     15:00     invalid
Day3     23:00     invalid

I want to create an SQL query to calculate the validity percentage for each day. The outcome should look like this:

date     percentage
--------------------
Day1     66
Day2     100
Day3     0

Below is my failed attempt to get the outcome. In this query, everything seems OK except the part (SELECT Count(validity) From myTable WHERE validity= 'valid') causes wrong result because it would count the whole row without grouping it by date.

SELECT date, ((SELECT Count(validity) From myTable WHERE validity= 'valid') * 100 / Count(validity)) as percentage
FROM myTable 
GROUP BY date

Upvotes: 2

Views: 1586

Answers (3)

Nick
Nick

Reputation: 147176

You can use conditional aggregation to take the average of only the valid values:

SELECT date,
       AVG(CASE WHEN validity = 'valid' THEN 100.0 ELSE 0.0 END) AS percentage
FROM myTable
GROUP BY date

Output:

date    percentage
Day1    66.6666
Day2    100
Day3    0

This query will run on MySQL or SQL Server.

Upvotes: 1

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following using SUM with CASE. There is no need to use a sub-query to get the count of valid rows.

SELECT date, (SUM(CASE WHEN validity = 'valid' THEN 1 ELSE 0 END) * 100 / COUNT(validity)) as percentage
FROM myTable 
GROUP BY date

Your current sub-query gets the count of all valid rows independent of the groups. You can use a conditional aggregation to get the count / sum of rows per group using COUNT / SUM with CASE.

demo on dbfiddle.uk

Upvotes: 1

George Joseph
George Joseph

Reputation: 5922

Here is a way to accomplish this

select date
       ,count(case when validity='valid' then 1 end)*100.00
        /
        count(validity) as percent
  from table
group by date

Upvotes: 0

Related Questions