| Subject | Re: [firebird-php] SQL query help to generate tota - subtotal and grand total |
|---|---|
| Author | David |
| Post date | 2009-09-16T20:13:08Z |
Hi Lester:
thanks in advance for the time I really apreaciated
I'm sorry to bother you but I'm trying to put a page break to this report
but I have 4 different queries and I need to make only one.
And the problem I cannot get to make this work with the queries below.
that why I was trying to make only one query
here is my 4 queries:
$Q1 = ibase_query ("select distinct(SERVE_DATE) from TRANS t inner join STUDENT s on t.STUD_NO=s.STUD_NO where t.GENE=0 and t.ORDERED=1 $AndFromDate $AndToDate order by SERVE_DATE");
$Q2 = ibase_query ("select distinct(AM_ROOM) from STUDENT");
$Q3 = ibase_query ("select t.STUD_NO, s.LAST_NAME , s.FIRST_NAME, s.GRADE from TRANS t inner join STUDENT s on t.STUD_NO=s.STUD_NO where t.GENE=0 and t.ORDERED=1 and s.AM_ROOM='$R2[0]' and t.SERVE_DATE='$R1[0]' $AndFromDate $AndToDate group by t.STUD_NO, s.LAST_NAME, s.FIRST_NAME, s.GRADE, t.SERVE_DATE order by t.SERVE_DATE, s.LAST_NAME, s.FIRST_NAME");
$Q4 = ibase_query ("select DESCRIPTION, sum(QUANTITY) from TRANS where STUD_NO=$R3[0] and GENE=0 AND ORDERED=1 and SERVE_DATE='$R1[0]' group by DESCRIPTION order by DESCRIPTION");
Lunch Date: 09/14/09
AM Room: 1-210 -- Lunch Date: 09/14/09
Last Name First Name Order
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
1 X Vegetable Soup prepared with Fresh Vegetables with Crackers
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
1 X Vegetable Soup prepared with Fresh Vegetables with Crackers
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
Total:
11 X French Toast (3 pieces) with Maple Syrup - Dairy
2 X Vegetable Soup prepared with Fresh Vegetables with Crackers
-----------------------------------------------------------------------
Lunch Date: SERVE_DATE
AM ROOM: AM_ROOM | LUNCH DATE: SERVE_DATE
LAST NAME | FIRST NAME | ORDER(quantity) | sub_total | grand total
------------------------------------------------------------------
las_name firts_name | 1 x chinese food | |
las_name firts_name | 1 x yogurt | 2 | 2
------------------------------------------------------------------
las_name firts_name | 2 x chinese food | |
las_name firts_name | 3 x yogurt | 5 | 5
------------------------------------------------------------------
total:
3 x chinse food
4 x yogurt
thanks in advance for the time I really apreaciated
I'm sorry to bother you but I'm trying to put a page break to this report
but I have 4 different queries and I need to make only one.
And the problem I cannot get to make this work with the queries below.
that why I was trying to make only one query
here is my 4 queries:
$Q1 = ibase_query ("select distinct(SERVE_DATE) from TRANS t inner join STUDENT s on t.STUD_NO=s.STUD_NO where t.GENE=0 and t.ORDERED=1 $AndFromDate $AndToDate order by SERVE_DATE");
$Q2 = ibase_query ("select distinct(AM_ROOM) from STUDENT");
$Q3 = ibase_query ("select t.STUD_NO, s.LAST_NAME , s.FIRST_NAME, s.GRADE from TRANS t inner join STUDENT s on t.STUD_NO=s.STUD_NO where t.GENE=0 and t.ORDERED=1 and s.AM_ROOM='$R2[0]' and t.SERVE_DATE='$R1[0]' $AndFromDate $AndToDate group by t.STUD_NO, s.LAST_NAME, s.FIRST_NAME, s.GRADE, t.SERVE_DATE order by t.SERVE_DATE, s.LAST_NAME, s.FIRST_NAME");
$Q4 = ibase_query ("select DESCRIPTION, sum(QUANTITY) from TRANS where STUD_NO=$R3[0] and GENE=0 AND ORDERED=1 and SERVE_DATE='$R1[0]' group by DESCRIPTION order by DESCRIPTION");
Lunch Date: 09/14/09
AM Room: 1-210 -- Lunch Date: 09/14/09
Last Name First Name Order
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
1 X Vegetable Soup prepared with Fresh Vegetables with Crackers
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
1 X Vegetable Soup prepared with Fresh Vegetables with Crackers
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
last name first 1 X French Toast (3 pieces) with Maple Syrup - Dairy
Total:
11 X French Toast (3 pieces) with Maple Syrup - Dairy
2 X Vegetable Soup prepared with Fresh Vegetables with Crackers
-----------------------------------------------------------------------
Lunch Date: SERVE_DATE
AM ROOM: AM_ROOM | LUNCH DATE: SERVE_DATE
LAST NAME | FIRST NAME | ORDER(quantity) | sub_total | grand total
------------------------------------------------------------------
las_name firts_name | 1 x chinese food | |
las_name firts_name | 1 x yogurt | 2 | 2
------------------------------------------------------------------
las_name firts_name | 2 x chinese food | |
las_name firts_name | 3 x yogurt | 5 | 5
------------------------------------------------------------------
total:
3 x chinse food
4 x yogurt
--- On Tue, 9/15/09, Lester Caine <lester@...> wrote:
> From: Lester Caine <lester@...>
> Subject: Re: [firebird-php] SQL query help to generate tota - subtotal and grand total
> To: firebird-php@yahoogroups.com
> Date: Tuesday, September 15, 2009, 3:29 PM
> 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
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
> mailto:firebird-php-fullfeatured@yahoogroups.com
>
>
>