joining two columns sql query-CodePudding

world! I'm currently stuck on this problem where i want to join two columns and run the select statement of the two, but i'm getting errors; these are the columns i want to join:

SELECT DISTINCT column_name FROM owner_name.table_name ORDER BY column_name;

and

SELECT DISTINCT * FROM (SELECT count(column_name) OVER (partition by column_name) Amount from owner_name.table_name order by column_name);

where in the second, for every row, i count how many equal rows i have for each value.

the two columns values: first column second column

i dont know how to have both of them next to each other as a normal select statement: SELECT column_1, column_2 FROM table;

CodePudding user response：

SELECT DISTINCT
       column_name,
       COUNT(column_name) OVER (PARTITION BY column_name) Amount 
FROM owner_name.table_name 
ORDER BY column_name;

CodePudding user response：

You do not want to use an analytic function for this as you will find the COUNT for all the rows and then use DISTINCT to discard rows which involves lots of unnecessary calculation.

Instead, it is much more efficient GROUP BY the column_name and then aggregate so that you only generate a single row for each group to start with:

SELECT column_name,
       COUNT(column_name) AS amount
FROM   owner_name.table_name
GROUP BY column_name
ORDER BY column_name;