pirulo64 wrote:

> I'm new to sql trying to generate a report can any one help me please
>
> here is my sql statement:
> I need to get the total at the end of the report with the totals of each am_room any help will really apreaciated.
>
> thanks
>
>
> select distinct(SERVE_DATE),(AM_ROOM),(S.last_name),(S.first_name),(T.description), sum(quantity) as qtotal
> from TRANS
> t inner join STUDENT s on t.STUD_NO=s.STUD_NO
> where t.GENE=0 and t.ORDERED=1
> group by
> serve_date,
> am_room,
> S.last_name,
> S.first_name,
> T.description
> order by SERVE_DATE
>
>
>
> Lunch Date: SERVE_DATE
> AM ROOM: AM_ROOM | LUNCH DATE: SERVE_DATE
>
> LAST NAME | FIRST NAME | ORDER(quantity)
> -----------------------------------------
> las_name firts_name | 1 x chinese food
> SAME firts_name | 1 x yogurt
> ------------------------------------------
> las_name firts_name | 2 x chinese food
> | 3 x yogurt
> ------------------------------------------
> total:
> 3 x chinse food
> 4 x yogurt
>
> I TRY THIS BUT IS NOT WORKING
> SELECT S.AM_ROOM, T.description, SUM(T.quantity) AS dtotal
> FROM TRANS AS T, Student AS S
> WHERE T.STUD_NO = S.STUD_NO
> GROUP BY S.AM_ROOM, T.description

You do not say what the problem is ...
But I'd start with a JOIN ...

SELECT S.AM_ROOM, T.description, SUM(T.quantity) AS dtotal
FROM TRANS T
JOIN Student S ON T.STUD_NO = S.STUD_NO
GROUP BY S.AM_ROOM, T.description

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php