Reputation: 30895
I have this query :
SELECT a.SELECTEDID contentId,
a.SELECTED_COUNT selectedCount,
a.P_ID
from (
SELECT t1.SELECTEDID,
ROUND(SUM(t1.SELECTED_COUNT*(2/3))) SELECTED_COUNT ,
t1.P_ID
FROM A_SEARCH t1
WHERE t1.P_ID = '11'
and t1.PERIOD >= ADD_MONTHS(TO_DATE('05/01/2018 12:00 AM', 'MM/DD/YYYY HH12:MI AM'), -3)
AND t1.PERIOD < TO_DATE('05/01/2018 12:00 AM', 'MM/DD/YYYY HH12:MI AM')
GROUP BY t1.P_ID, t1.SELECTEDID
) a
LEFT JOIN
(
SELECT t2.SELECTEDID,
ROUND(SUM(t2.SELECTED_COUNT*2)) SELECTED_COUNT ,
t2.P_ID
FROM A_SEARCH t2
WHERE t2.P_ID ='11'
and t2.PERIOD >= TO_DATE('05/01/2018 12:00 AM', 'MM/DD/YYYY HH12:MI AM')
GROUP BY t2.P_ID, t2.SELECTEDID
) b
on b.P_ID = a.P_ID
ORDER BY a.SELECTED_COUNT desc;
The table :
P_ID(VARCHAR2),
P_VALUE(VARCHAR2),
SELECTEDID(VARCHAR2),
SELECTED_COUNT(NUMBER),
PERIOD(TIMESTAMP)
This row always selected
11, bb,test22, 2, 01-APR-18 12.00.00.000000000 AM
This row never selected
11, aa, test, 2, 01-JUN-18 12.00.00.000000000 AM
I try to make multiple selects from single table and then order by the results.
But somehow only the first select in the query is successfuk and the first row above is selected.
But the second LEFT JOIN doesn't catch the second row and I can't see what is wrong.
How can I select both rows ?
Upvotes: 0
Views: 52
Reputation: 48111
You are only selecting columns from a
for your final output:
SELECT a.SELECTEDID contentId, a.SELECTED_COUNT selectedCount, a.P_ID
If a row from b
matches the join condition, you would need to also select columns from b
in order to display them. They would display on the same row as the columns from the matching row in a
.
You say
I try to make multiple selects from single table and then order by the results
This implies you want to get separate output rows for the results of the a
and b
subqueries, which means you don't really want a join.
You may want a UNION
of the two subqueries instead.
Upvotes: 2
Reputation: 1269763
From what I can tell, your query is equivalent to:
SELECT t1.SELECTEDID, ROUND(SUM(t1.SELECTED_COUNT*(2/3))) as SELECTED_COUNT, t1.P_ID
FROM A_SEARCH t1
WHERE t1.P_ID = 11 AND -- I removed the single quotes, assuming the column is a number
t1.PERIOD >= ADD_MONTHS(DATE '2018-05-01', -3) AND
t1.PERIOD < DATE '2018-05-01'
GROUP BY t1.P_ID, t1.SELECTEDID ;
The LEFT JOIN
might multiply the number of rows; I don't see that as desirable.
Also note the simplification of the date
literals, by using the date
keyword.
Upvotes: 0