Simple SQL ??!!

[abutler]

Hi

I have an sql script that collects stats on our users on a daily basis. The problem I have is that I can get it to add up the columns per day but I need to then be able to add up all the days to give me a weekly total and I don't know how! I have to spend hours doing it in excel, anybody got any ideas??

E.G
Mon Tue Wed Thu Fri Sat Sun
Jones 0 1 2 3 4 5 6
Smith 3 2 0 1 3 1 0
---------------------------
3 3 2 4 7 6 6 How do I get sql to add
this line ??????

Thanks Ali

[Sameer]

Using O9i??

You can use ROLLUP or CUBE functions..

Sameer

[pando]

rollup and cube avaibale from 8i

[Sameer]

Originally posted by pando
rollup and cube avaibale from 8i

Yes..

Sorry I wasn't aware..

[abutler]

How?? I'm not sure how the syntax works for these. Are there any examples anywhere to show me how to write the sql?

Using 8i.


Thanks Ali

[Sameer]

Heres a result...

Count by City, Name and total Count

Using ROLLUP

SQL> SELECT name, city, count(city)
  2  FROM test
  3  GROUP BY ROLLUP(name,city)
  4  ORDER BY name;

NAME       CITY       COUNT(CITY)
---------- ---------- -----------
Bob        Düsseldorf           1
Bob        Frankfurt            4
Bob                             5
Jack       Düsseldorf           1
Jack       Münich               1
Jack                            2
Mel        Düsseldorf           1
Mel        Münich               1
Mel                             2
Nichol     Berlin               1
Nichol     Frankfurt            1
Nichol                          2
Sameer     Berlin               1
Sameer     Düsseldorf           1
Sameer     Frankfurt            3
Sameer     Münich               1
Sameer                          6

NAME       CITY       COUNT(CITY)
---------- ---------- -----------
                               17
SQL>

Using CUBE

SQL> SELECT name, city, count(city)
  2  FROM test
  3  GROUP BY CUBE(name,city)
  4  ORDER BY name;

NAME       CITY       COUNT(CITY)
---------- ---------- -----------
Bob        Düsseldorf           1
Bob        Frankfurt            4
Bob                             5
Jack       Düsseldorf           1
Jack       Münich               1
Jack                            2
Mel        Düsseldorf           1
Mel        Münich               1
Mel                             2
Nichol     Berlin               1
Nichol     Frankfurt            1
Nichol                          2
Sameer     Berlin               1
Sameer     Düsseldorf           1
Sameer     Frankfurt            3
Sameer     Münich               1
Sameer                          6

NAME       CITY       COUNT(CITY)
---------- ---------- -----------
           Berlin               2
           Düsseldorf           4
           Frankfurt            8
           Münich               3
                               17

22 rows selected.

SQL>

HTH

Sameer

[Edited by Sameer on 09-20-2002 at 08:46 AM]

[abutler]

What is the difference between rollup & cube, when would you use one over the other?

Ali