当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Joining two select statements and aligning them

问题描述:

I'm have one table that looks like this:

+-----------+--------------+---------------------+

| ENTRY_ID | EMPLOYEE_ID | ACCESS_TIME |

+-----------+--------------+---------------------+

| 001 | 1001 | 6/3/2014 8:08:00 AM |

+-----------+--------------+---------------------+

| 777 | 1201 | 8/3/2015 7:07:00 AM |

+-----------+--------------+---------------------+

| 888 | 1901 | 9/9/2015 4:04:00 PM |

+-----------+--------------+---------------------+

In order to get the number of unique users for a particular date range, I have the following code:

SELECT COUNT(UNIQUE Employee_Id) Unique_Monthly_Users FROM

(SELECT * FROM CORE.DATE_TEST

WHERE ACCESS_TIME >= TO_DATE('8/1/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

AND ACCESS_TIME < TO_DATE('9/1/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

ORDER BY ACCESS_TIME ASC)

And to get the current month for that range, I'm using this:

SELECT UNIQUE TO_CHAR(ACCESS_TIME, 'Month') Month_Name FROM (

SELECT * FROM CORE.DATE_TEST

WHERE ACCESS_TIME >= TO_DATE('8/1/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

AND ACCESS_TIME < TO_DATE('9/1/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

ORDER BY ACCESS_TIME ASC)

Here's my final desired output:

+--------------+-----------+

| UNIQUE_USERS | MONTH |

+--------------+-----------+

| 702931 | JANUARY |

+--------------+-----------+

| 900000 | FEBRUARY |

+--------------+-----------+

| 2139041022 | MARCH |

+--------------+-----------+

Now I've tried to join these statements:

SELECT COUNT(UNIQUE EMPLOYEE_ID) Unique_Emp FROM

((SELECT * FROM CORE.DATE_TEST

WHERE ACCESS_TIME >= TO_DATE('8/1/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

AND ACCESS_TIME < TO_DATE('9/1/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

ORDER BY ACCESS_TIME ASC) col1)

INNER JOIN (SELECT UNIQUE TO_CHAR(ACCESS_TIME, 'Month') Month_Name FROM (

(SELECT * FROM CORE.DATE_TEST

WHERE ACCESS_TIME >= TO_DATE('8/1/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

AND ACCESS_TIME < TO_DATE('9/1/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

ORDER BY ACCESS_TIME ASC

) col2)

) ON COL1.ENTRY_ID = COL2.ENTRY_ID

...but obviously this will not work. I am not able to get the "id" because there should not be an ID for either result set. Both are collections which exist outside. I get the following error: ORA-00904: "COL2"."ENTRY_ID": invalid identifier

How do I properly join these result sets so I can get the desired output?

网友答案:

Look up the GROUP BY clause and then try this:

select to_char(access_time, 'Month'), count(distinct employee_id)
from core.date_test
where usage_time >= TO_DATE('8/1/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
and usage_time < TO_DATE('9/1/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
group by to_char(access_time, 'Month');
网友答案:

You can do this with two clauses, the first one selected distinct users for each month and the second one counts them:

SELECT
    COUNT(*) AS UNIQUE_USERS,
    T1.MONTH
FROM
(
    SELECT DISTINCT
        USER_ID,
        TO_CHAR(ACCESS_TIME) AS MONTH
    FROM DATE_TEST
) AS T1
GROUP BY T1.MONTH
分享给朋友:
您可能感兴趣的文章:
随机阅读: