SQL - Why does my output from subqueries display my answer multiple times?

41
November 12, 2018, at 10:00 PM

The output to the following query is repeated for the number of rows in my table. Can anyone explain why and if using 'Limit 1' to solve this is a good habit to get into?

SELECT
CAST((SELECT COUNT(event_id)
FROM creation_funnel
WHERE Ds = 2018-10-01 AND action = "publish") AS float) / 
CAST((SELECT COUNT(DISTINCT event_id)
FROM creation_funnel
WHERE Ds = 2018-10-01) AS float)
 * 100.0
FROM creation_funnel;
Answer 1

This query would be a good candidate for conditional aggregation as it will enable you to get rid of the two subqueries. Try this instead:

SELECT
    COUNT(CASE WHEN Ds = '2018-10-01' AND action = 'publish' THEN event_id END) /
    COUNT(DISTINCT CASE WHEN Ds = '2018-10-01' THEN event_id END) * 100 AS percent_published
FROM creation_funnel

Note that date strings need to be enclosed in quotes, as @Strawberry points out, 2018-10-01 is interpreted as an integer expression, evaluating to 2007.

This query will also only give you one result compared to your query, which will give you a result for every row in the table. Either removing the FROM clause, or adding LIMIT 1 would resolve that, but using conditional aggregation is a better alternative.

Answer 2
SELECT
CAST((SELECT COUNT(event_id) FROM creation_funnel WHERE Ds = '2018-10-01' AND action = "publish") AS float) /
CAST((SELECT COUNT(DISTINCT event_id) FROM creation_funnel WHERE Ds = '2018-10-01') AS float) * 100.0

The repetition is because of the FROM part of the statement. Remove it. Your are getting multiple results because the select is being run for each record in the table.

Based on what nick said. You could simplify his answer to the following.

SELECT 
    COUNT(CASE WHEN action = 'publish' THEN event_id ELSE 0 END) / 
    COUNT(DISTINCT event_id) * 100 AS percent_published
FROM creation_funnel
WHERE Ds = '2018-10-01';
READ ALSO
How to push pandas dataframe to SQL database using connection pool (Python 3)

How to push pandas dataframe to SQL database using connection pool (Python 3)

I'm a student who is very new to SQL technologies and I'm faced with the following problemI have a Pandas dataframe that is filled with categorical text data

82
Database repair through Wordpress

Database repair through Wordpress

I repaired my website through Wordpress repair and since then only my home page is workingI'm getting a 404 error message on all other pages of the site

33
AWS RDS MySQL 8.0 Parameter Groups

AWS RDS MySQL 8.0 Parameter Groups

We have recently upgraded from MySQL 57 to 8

80