conditionally sum from one table based on information from another table

Question

I am trying to write an sql query that outputs data for a python script. The python script will eventually push that data to a table so to make things smoother, I decided to cast the output as char.

The data that I have is organized by 15min periods. Data A and data B are stored on one table and have columns start_time (as a datetime), counts A, and counts B. The second table has start_time (as a datetime), and counts C.

What I need is sum for A, B, and C for each day. However, I want to sum conditionally where it only counts in the sum where the other two data counts are not NULL for that 15 min period. For example, if a "row" for a 15 min period has data for A and B but not C, it would not count in the sum. How do I implement this conditional?

example output:

date| SUM(A) | SUM(B) | SUM(C)

I can write without the conditional like this (new to sql):

SELECT 
    DATE('timezone conversion') AS date,

    cast(SUM(p1.COUNT_DATA_A) as char)
    AS A,

    cast(SUM(p1.COUNT_DATA_B) as char)
    AS B,

    cast(SUM(p2.COUNT_DATA_C) as char)
    AS C
FROM
    table_data_A_B
    AS p1

    LEFT JOIN table_data_C 
    AS p2 ON p1.start_time = p2.start_time

WHERE
    DATE('timezone conversion') >= '2018-03-27'
        AND DATE('timezone conversion') < '2018-03-29'
GROUP BY DATE('timezone conversion')
ORDER BY DATE(p1.start_time) DESC

How would I implement the conditional in this query? I appreciate the help. I am a bit new to stackoverflow, coding and sql in general but I will try my best to be helpful.

Do you mean p2.COUNT_DATA_C is null OR table_data_C has no start_time for a given 15min period? — jose_bacoy, Apr 05 '18 at 00:12
@âńōŋŷXmoůŜ when count_data_c is null. start_time is a primary key so it should never be null i think. — Aaron Arima, Apr 05 '18 at 00:39
Thanks for your reply. Barmar has answered your question and it is correct — jose_bacoy, Apr 05 '18 at 00:43

score 1 · Accepted Answer · edited Apr 05 '18 at 19:57

1

Just test for this in the WHERE clause of the query.

WHERE DATE('timezone conversion') BETWEEN '2018-03-27' AND '2018-03-29'
AND p1.COUNT_DATA_A IS NOT NULL AND p1.COUNT_DATA_B IS NOT NULL AND p2.COUNT_DATA_C IS NOT NULL

edited Apr 05 '18 at 19:57

jose_bacoy

12,227
1
20
38

answered Apr 05 '18 at 00:02

Barmar

741,623
53
500
612

typo error on AND p1.COUNT_DAA_B but I know you will find it later. – jose_bacoy Apr 05 '18 at 00:42
@âńōŋŷXmoůŜ this seems to eliminate dates where count_data_A = null for the entire day. so if there is no 15 min data for that day, the entire row is not displayed. Is there a way so that those rows will show up but just report NULL for the sum? – Aaron Arima Apr 06 '18 at 22:35
@AaronArima See https://stackoverflow.com/questions/2157282/generate-days-from-date-range – Barmar Apr 06 '18 at 22:39
so the solution is to add rows that are missing in the data set? I'm a bit confused on how the linked question relates to mine – Aaron Arima Apr 07 '18 at 00:01
No, the solution is to left join with a table that contains all the times you want. – Barmar Apr 07 '18 at 00:05

conditionally sum from one table based on information from another table

1 Answers1