Omit null rows from query with conditional expressions
Is there a way to omit rows where both "CREATE" AND "DELETE" are NULL? Here's my query, any help would be much appreciated!
This is for a PostgreSQL database.*
SELECT
jivedw_container.container_id as "ID", jivedw_day.year as "Year",
jivedw_day.week_of_year as "Week Of", jivedw_container.name as "Community",
jivedw_activity_agg_user_day.direct_object_type as "Object",
( jivedw_container.name
|| '@'
|| To_char(CURRENT_DATE- INTERVAL '138 day', 'mm/dd/yy') ) AS
"Object Link",
sum(CASE WHEN jivedw_activity_agg_user_day.activity_type = '20' THEN 1 ELSE NULL END)
as "CREATE",
sum(CASE WHEN jivedw_activity_agg_user_d开发者_运维技巧ay.activity_type = '30' THEN 1 ELSE NULL END)
as "DELETE"
FROM jivedw_activity_agg_user_day
INNER JOIN jivedw_day on jivedw_activity_agg_user_day.day_id = jivedw_day.day_id
INNER JOIN jivedw_container on jivedw_activity_agg_user_day.dw_container_id =
jivedw_container.dw_container_id
WHERE (jivedw_day.begin_ts BETWEEN CURRENT_DATE - INTERVAL '138 day' AND CURRENT_DATE - INTERVAL '132 day')
AND (jivedw_activity_agg_user_day.direct_object_type IN (1,2,37,38, 102, 1100))
GROUP BY
jivedw_container.container_id,
jivedw_container.name,
jivedw_day.year,
jivedw_day.week_of_year,
jivedw_activity_agg_user_day.direct_object_type
ORDER BY jivedw_container.container_id, jivedw_container.name
;
SELECT * FROM (SELECT
jivedw_container.container_id as "ID", jivedw_day.year as "Year",
jivedw_day.week_of_year as "Week Of", jivedw_container.name as "Community",
jivedw_activity_agg_user_day.direct_object_type as "Object",
( jivedw_container.name
|| '@'
|| To_char(CURRENT_DATE- INTERVAL '138 day', 'mm/dd/yy') ) AS "Object Link",
sum(CASE WHEN jivedw_activity_agg_user_day.activity_type = '20' THEN 1 ELSE NULL END)
as "CREATE",
sum(CASE WHEN jivedw_activity_agg_user_day.activity_type = '30' THEN 1 ELSE NULL END)
as "DELETE"
FROM jivedw_activity_agg_user_day
INNER JOIN jivedw_day on jivedw_activity_agg_user_day.day_id = jivedw_day.day_id
INNER JOIN jivedw_container on jivedw_activity_agg_user_day.dw_container_id =
jivedw_container.dw_container_id
WHERE (jivedw_day.begin_ts BETWEEN CURRENT_DATE - INTERVAL '138 day' AND CURRENT_DATE - INTERVAL '132 day')
AND (jivedw_activity_agg_user_day.direct_object_type IN (1,2,37,38, 102, 1100))
GROUP BY
jivedw_container.container_id,
jivedw_container.name,
jivedw_day.year,
jivedw_day.week_of_year,
jivedw_activity_agg_user_day.direct_object_type
ORDER BY jivedw_container.container_id, jivedw_container.name
;) as SUB_QUERY WHERE SUB_QUERY.CREATE IS NOT NULL AND SUB_QUERY.DELETE is NOT NULL
This should work
精彩评论