Geek asks and answers

How to group a column

Hi, I know how to use group by clause for sql. I am not sure how to explain this, so I draw some diagrams. Here are my raw data:

Name          Location
----------------------
user1         1
user1         9
user1         3
user2         1
user2         10
user3         97

Here is the result I need

Name          Location
----------------------
user1         1
              9
              3
user2         1
              10
user3         97

Is it possible?

+3
source share
7 answers

Hope this helps


SELECT A.FINAL_NAME, A.LOCATION
  FROM (SELECT DISTINCT DECODE((LAG(YT.NAME, 1) OVER(ORDER BY YT.NAME)),
                               YT.NAME,
                               NULL,
                               YT.NAME) AS FINAL_NAME,
                        YT.NAME,
                        YT.LOCATION
          FROM YOUR_TABLE_7 YT) A

As Jirka correctly pointed out, I used the optional selection Outer select, different and raw Name. My mistake was that when I used DISTINCT, I got sorted


1           1
2   user2   1
3   user3   97
4   user1   1
5           3
6           9
7          10

I wanted to avoid the output as follows.

Consequently, I added the raw id and external selection

However, removing DISTINCT solves the problem. Therefore, this is sufficient only


SELECT DECODE((LAG(YT.NAME, 1) OVER(ORDER BY YT.NAME)),
              YT.NAME,
              NULL,
              YT.NAME) AS FINAL_NAME,
       YT.LOCATION
  FROM SO_BUFFER_TABLE_7 YT

Thanks Jirka

+3
source

- , .

:

  • Name
  • Name , , Location ?
  • , SQL ( )
  • , SQL - SETS
+5

SQL * Plus, ( , ), BREAK:

SQL> break on name
SQL> WITH q AS (
SELECT 'user1' NAME, 1 LOCATION FROM dual
UNION ALL
SELECT 'user1', 9 FROM dual
UNION ALL
SELECT 'user1', 3 FROM dual
UNION ALL
SELECT 'user2', 1 FROM dual
UNION ALL
SELECT 'user2', 10 FROM dual
UNION ALL
SELECT 'user3', 97 FROM dual
)
SELECT NAME,LOCATION
  FROM q
 ORDER BY name;

NAME    LOCATION
----- ----------
user1          1
               9
               3
user2          1
              10
user3         97

6 rows selected.

SQL>
+2

, , SQL, .

SELECT
    CASE main.name WHERE preceding_id IS NULL THEN main.name ELSE null END,
    main.location
FROM mytable main LEFT JOIN mytable preceding
    ON main.name = preceding.name AND MIN(preceding.id) < main.id
GROUP BY main.id, main.name, main.location, preceding.name
ORDER BY main.id

GROUP BY , , . (LEFT JOIN ) , . , . , id, . ( ORDER BY : , , , , SQL - .)

, SQL GROUP BY, . - "" LEFT JOIN, , "" (= ) .

GROUP BY. GROUP BY - ; , GROUP BY, , COUNT, MIN, MAX SUM. , " " - GROUP BY, .

+1

SQL * Plus, BREAK. NAME.

, "" , .

+1

GROUP BY, GROUP BY, ORDER BY . , GROUP BY, ORDER BY NULL:

SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

GROUP BY MySQL 5.6, . ORDER BY. GROUP BY - MySQL, ; , , , .

- http://academy.comingweek.com/sql-groupby-clause/

+1

SQL GROUP SQL GROUP BY SELECT . : 1. SELECT column_nm, aggregate_function (column_nm) FROM table_nm WHERE column_nm GROUP BY column_nm; : GROUP BY clauserefer, . Bowow, "order": 1. | EMPORD_ID | employee1ID | customerID | shippers_ID |

, "": 1. | shippers_ID | shippers_Name |

, "table_emp1": 1. | employee1ID | first1_nm | last1_nm |

: , . 1. SELECT shipper.shippers_Name, COUNT (orders.EMPORD_ID) AS No_of_orders FROM orders LEFT JOIN shipper ON orders.shippers_ID = shipper.shippers_ID GROUP BY shippers_Name; 1. | shippers_Name | No_of_orders | : GROUP BY . 1. SELECT shipper.shippers_Name, table_emp1.last1_nm, COUNT (orders.EMPORD_ID) AS No_of_orders FROM (( INNER JOIN shipper ON orders.shippers_ID = shipper.shippers_ID) INNER JOIN table_emp1 ON orders.employee1ID = table_emp1.employee1ID) 2. GROUP BY shippers_Name, last1_nm;

  • | shippers_Name | last1_nm | No_of_orders |

http://academy.comingweek.com/sql-groupby-clause/

0

More articles:


All Articles