ansaurus

Question

on sql basic question

Answer 1

A:

select sal from empsal union select SUM(sal) from empsal
I think you want to get the count of columns for a table. You can do that be following query:

select count(*) from information_schema.columns where table_name = ''

Chinjoo 2010-06-17 06:50:46

There is only a need to scan the table twice when the original poster has an Oracle database version < 8i. Somewhere in the 8i range, the group by extensions became available. And when you do it the Oracle7 way, then at least use UNION ALL instead of UNION to avoid an unnecessary sorting step and to avoid the possibility of losing the total when only one row is present in the table.

Rob van Wijk 2010-06-17 08:31:52

-1 And although I don't understand question two, your query in response is nonsense. It should be "select count(*) from user_tab_columns where table_name = 'his_single_table'".

Rob van Wijk 2010-06-17 08:33:51

oops... I din noticed the tag... my answer was for SQL

Chinjoo 2010-06-17 09:32:27

Answer 2

A:

Solution 1: Use Roll Up Function for first question

select empno,sum(sal) from emp group by rollup((sal,empno));

Solution 2: Use Grouping Sets

select empno,sum(sal) from emp group by GROUPING SETS ((empno),())

Bharat 2010-06-17 07:32:21

This doesn't produce the expected output, although the rollup suggestion is a good one.

Rob van Wijk 2010-06-17 08:13:37

@Rob van Wijk: You are right, i havent read the question properly.

Bharat 2010-06-17 08:32:45

Answer 3

+1 A:

Q1:

SQL> select sum(sal)
  2    from emp
  3   group by rollup(empno)
  4  /

  SUM(SAL)
----------
       800
      1100
      1300
      1500
      1600
      3000
      3000
      5000
       950
      1250
      1250
      2450
      2850
      2975
     29025

15 rows selected.

Q2: Please explain this question some more.

Regards, Rob.

Rob van Wijk 2010-06-17 08:13:15

ansaurus

tags:

views:

answers:

on sql basic question

related questions