Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am not sure how to approach this, I want to create a view

The columns are FullName, StudentID, HoursPassed, HoursFailed and HoursPassed+HoursFailed of students majoring in Finance.

BUT my column headings need to be FullName, StudentID, HoursPassed, HoursFailed and HoursAttempted

So far I have:

SQL
CREATE VIEW A5T7 AS
    SELECT (firstname || ' ' || lastname) AS "FullName", StudentID, HoursPassed, HoursFailed, (HoursPassed + HoursFailed as sum) AS HoursAttempted
    FROM A5
    GROUP BY Upper(Major1='FIN'), Upper(Major2='FIN')
    ORDER BY HoursAttempted;


I am pretty sure my query is wrong, somewhere. I just don't know how to approach the Heading Name and the SUM value. Also my GROUP BY doesn't seem right.
Posted

1 solution

Group it on the StudentId column:
SQL
CREATE VIEW A5T7 AS
    SELECT (firstname || ' ' || lastname) AS FullName, StudentID, HoursPassed, HoursFailed, (HoursPassed + HoursFailed) AS HoursAttempted
    FROM A5
    WHERE Upper(Major1)='FIN' OR Upper(Major2)='FIN'
    ORDER BY HoursAttempted;


[Edit]
In fact, since there is no join in the query to calculate the sum of values of related records, you don't even need a GROUP BY clause.
I updated the code.
[/Edit]
 
Share this answer
 
v3
Comments
Member 11786973 1-Jul-15 22:22pm    
And how do I restrict it for FIN major students only?
phil.o 1-Jul-15 22:26pm    
I don't know your table schema, so I do not have a clue about what a FIN Major is and how it is represented in your table.
If you want to restrict, however, a WHERE clause is the way to go; it should be placed between the FROM clause and the GROUP BY clause.
phil.o 1-Jul-15 22:39pm    
I edited the solution as it appears you do not need a GROUP BY clause (all columns are in the same table and there is no aggregation function used).
Member 11786973 1-Jul-15 23:00pm    
but it wont let me enter it without the Group By function and I need the SUM to calculate the last column
phil.o 2-Jul-15 0:13am    
You only need a GROUP BY clause when you use an aggregation function. I can't see any aggregation function. Could you clarify briefly the colums of your table, and give a short example of its content? Is there any other table involved?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900