'not a single group-group function' error in SQL

Question

I'm doing some revision on uni work where we're dealing with a data warehousing exercise for a simple scenario, the data warehouse is being built to tally the number of times student use a computer lab based on different time periods, semesters and majors. As a reference, our data warehouse is built from four original tables which are:

• USELOG(log_date, log_time, Student_ID, Act)
• Student(Student_ID, Sex, Full/Part, Type, Class_ID, Major_Code)
• Major(Major_name, Major_code)Major(Major_name, Major_code)
• Class(Class_description, Class_ID)

I get two errors, one I worked around from (I think) and another one is a group by error that I can't seem to work out as I have the teacher's solutions and we have similar groupings but I guess there's is something different about how I've done the task.

Anyway, my code is as follows:

Create table Period_DIM (
PeriodID number,
PeriodDesc varchar2(15),
begin_time date,
end_time date);

Create Table Semester_DIM(
SemesterID number,
Semester_Desc varchar2(15),
start_date date,
end_date date);

Create Table Major_DIM as Select * from major;

Create Table class_dim as select * from class;

--Populate Period_Dim
Insert Into Period_DIM Values(1, 'Morning', To_date('06:01', 'HH24:MI'), To_date('12:00', 'HH24:MI'));
Insert Into Period_DIM Values(2, 'Afternoon', To_date('12:01', 'HH24:MI'), To_date('18:00', 'HH24:MI'));
Insert Into Period_DIM Values(3, 'Evening', To_date('18:01', 'HH24:MI'), To_date('06:00', 'HH24:MI'));

--Populate Semester_DIM
Insert Into Semester_DIM Values (1, 'Semester 1', To_date('01-Jan', 'DD-MON'), To_date('15-JUL', 'DD-MON'));
Insert Into Semester_DIM Values (2, 'Semester 2', To_date('16-Jul', 'DD-MON'), To_date('31-DEC', 'DD-MON'));

--Create Temp Fact Table
Create table tempfacttable As Select u.LOG_DATE, u.LOG_TIME, u.STUDENT_ID, s.CLASS_ID, s.MAJOR_CODE
From Uselog u, student s
where u.STUDENT_ID = s.STUDENT_ID;

--Add a timeID to each row in the table because the data doesn't originally have them
alter table tempfacttable add (timeid number);
update tempfacttable
set timeid = 1
where to_char(log_time, 'HH24:MI') >= '06:01'
and to_char(log_time, 'HH24:MI') <= '12:00';

update tempfacttable
set timeid = 2
where to_char(log_time, 'HH24:MI') >= '12:01'
and to_char(log_time, 'HH24:MI') <= '18:00';

--Use OR in this case
update tempfacttable
set timeid = 3
where to_char(log_time, 'HH24:MI') >= '18:00'or to_char (log_time, 'HH24:MI') <= '06:00';

--Add a semester ID based on the date in the tempfact cause its not contained in the Dimensions
alter table tempfacttable add (semid varchar2(10));
update tempfacttable
set semid= 'S1'
where to_char(log_date, 'MMDD') >= '0101'
and to_char(log_date, 'MMDD') <= '0715';

update tempfacttable
set semid = 'S2'
where to_char(log_date, 'MMDD') >= '0716'
and to_char(log_date, 'MMDD') <= '1231';

--Create the Fact Table
Create table factTable as 
select t.SEMID, t.TIMEID, t.MAJOR_CODE, t.CLASS_ID, count(t.student_id) as total_usage
From tempfacttable t
group by t.semid, t.timeid, t.class_id, t.major_code;

select total_usage from tempfacttable; --returns error saying total_usage is an invalid operator

--Usage for time period by major and by student's class
--Select t.timeid, p.perioddesc, m.major_code, m.major_name, c.class_id, c.class_description, sum(t.total_usage) as usage_numbers 
--Previous line returns error because of t.total_usage, workaround is in the next line
Select t.timeid, p.PERIODDESC, m.MAJOR_CODE, m.MAJOR_NAME, c.CLASS_ID, c.CLASS_DESCRIPTION, sum(count(t.student_id)) as usage_numbers
from tempfacttable t, period_dim p, class_dim c, major_dim m
where t.timeid = p.PERIODID
and t.major_code = m.major_code
group by t.timeid, p.PERIODDESC, t.major_code, m.major_name, t.class_id, c.class_description;

I can't seem to get it working, I get an error stating that 'not a single group-group function'. I would really appreciate some help with this and with the reason as to why I can't refer to total_usage, even though if I select * from table containing it it gets displayed with the rest of the attributes. Thank you so much

Answer 1

I think it's because you DO NOT have a total_usage column defined within your tempfacttable. You do, however, have a column total_usage in your factTable table

Try this instead:

select total_usage from factTable;