0

some quick questions.

I have this query:

SELECT u.Table_Name, u.Column_Name
FROM User_Tab_Columns u;

Which gives me these results:

TABLE_NAME                     COLUMN_NAME
------------------------------ -----------
BONUS                          ENAME
BONUS                          JOB
BONUS                          SAL
BONUS                          COMM
DEPT                           DEPTNO
DEPT                           DNAME
DEPT                           LOC
DUMMY                          DUMMY
EMP                            EMPNO
EMP                            ENAME
EMP                            JOB
EMP                            MGR
EMP                            HIREDATE
EMP                            SAL
EMP                            COMM
EMP                            DEPTNO
SALGRADE                       GRADE
SALGRADE                       LOSAL
SALGRADE                       HISAL

I'm unsure how to make it so that the table_name results do not repeat (so one of each result) and to have the number of columns that each table has in place of the column_name.

Thanks in advance for any help.

ShaunKelly01
  • 9
  • 4

1 Answers1

3

Using the GROUP BY clause, you can group your tables so that you can run an aggregate function, in this case COUNT, on the columns you wish to count.

SELECT u.Table_Name, COUNT(1) AS Column_Count FROM User_Tab_Columns u GROUP BY u.Table_Name
Jason W
  • 13,026
  • 3
  • 31
  • 62
  • 1
    why `count(1)` and not `count(*)`? –  Nov 20 '14 at 13:07
  • Just a habit, you can use `COUNT(*)` or even `COUNT(Column_Name)`. The `COUNT(*)` or `COUNT(1)` will perform a little better (no difference between the two to just count the number of rows for the group). – Jason W Nov 20 '14 at 13:08
  • It's almost magical. Thanks. Is there a way to add the number of rows in each table, too? – ShaunKelly01 Nov 20 '14 at 13:09
  • 1
    (I just wanted to make sure you aren't falling for the popular myth that `count(1)` is faster than `count(*)`) But `count(column_name)` will return something different than `count(*)` –  Nov 20 '14 at 13:09
  • Not necessarily. By itself and with no NULLs, it returns the same. But the columns can give you additional options, like counting non-null or distinct values. – Jason W Nov 20 '14 at 13:15
  • Without the optimizer, `COUNT(*)` would hydrate the whole row into memory when counting whereas `COUNT(1)` by nature does not. However, the optimizer transforms `COUNT(*)` to `COUNT(1)` prior to execution. – Jordan Parmer Nov 20 '14 at 13:18
  • 1
    `COUNT(column_name)` will return the count of non-null values for the column. `COUNT(1)` returns all rows for the entire result set. – Jordan Parmer Nov 20 '14 at 13:19
  • I agree completely about COUNT(1) and COUNT(*) being identical. Best writeup I've read is: http://stackoverflow.com/questions/1221559/count-vs-count1. I fell into the myth until I read that, and still have it as a habit. I only suggested COUNT(Column_Name) because you were going off a list of tables/columns...you wouldn't have a null column name. – Jason W Nov 20 '14 at 13:22
  • Is there a way to add the number of rows (like count(row) or some equivalent?) like the number of columns has been listed? – ShaunKelly01 Nov 20 '14 at 13:28
  • @ShaunKelly01 - Assuming statistics are relatively updated, in Oracle, you'd run `SELECT table_name, num_rows FROM user_tables`. In SQL Server to use the statistics, it's a longer query, but good blog here: http://weblog.kevinattard.com/2011/07/how-to-get-table-row-count-for-all.html – Jason W Nov 20 '14 at 13:29
  • Thanks. I input the SELECT query you gave, but the Num_Rows results were blank. – ShaunKelly01 Nov 20 '14 at 13:43
  • That's probably because your db doesn't have any stats computed for the tables. `ANALYZE TABLE table_name COMPUTE STATISTICS` computes the stats, but you should probably log a new question to get more eyes on it for best practices and when the stats update – Jason W Nov 20 '14 at 13:48
  • @JordanParmer _"Without the optimizer, `COUNT(*)` would hydrate the whole row into memory"_ If don't think so, as according SQL-92, `COUNT(*)` is the _cardinality_ of the table expression. The content of the row is irrelevant. – Sylvain Leroux Nov 20 '14 at 14:07
  • 1
    @SylvainLeroux I stand corrected. I looked it up - my previous understanding was wrong. – Jordan Parmer Nov 20 '14 at 16:42