Grouping with Page Break

I have a report that shows the monthwise details, i have performed this using the table control. the requirement was to have a page break after each month detail. End of each group display the monthwise total. This is absolutely working fine.

My problem is, I am suppose to display the Report total also. I used the Sum in the Table footer. The sum is coming fine the only problem that i have is it is printing on the a New Page instead of the Last Page.

Please suggest.

Hi,

you have to use the Group Footer, not the Table Footer. Edit Group -> include group footer:

Greez Daniel

|||

I have to display the Report Sub Total of the Group that i am already doing in the Group Footer, Where as I am wanting to display the Main Total of the Report, It wont work in the Group Footer as it is the Full Report total.

I tried to add a new Group fo the report just to have a main group and the other one as sub group, but it also did not work.

|||Try setting the RepeatOnNewPage property in the table footer to true. This will show the Main Total on every page.

Grouping with Page Break

I have a report that shows the monthwise details, i have performed this using the table control. the requirement was to have a page break after each month detail. End of each group display the monthwise total. This is absolutely working fine.

My problem is, I am suppose to display the Report total also. I used the Sum in the Table footer. The sum is coming fine the only problem that i have is it is printing on the a New Page instead of the Last Page.

Please suggest.

Hi,

you have to use the Group Footer, not the Table Footer. Edit Group -> include group footer:

Greez Daniel

|||

I have to display the Report Sub Total of the Group that i am already doing in the Group Footer, Where as I am wanting to display the Main Total of the Report, It wont work in the Group Footer as it is the Full Report total.

I tried to add a new Group fo the report just to have a main group and the other one as sub group, but it also did not work.

|||Try setting the RepeatOnNewPage property in the table footer to true. This will show the Main Total on every page.sql

Grouping and Custom Code

Hello everyone,

I've got an issue where I want to sum the group values and not the details, the reason is because I am hiding duplicate records. Here's how my Layout is setup.

TH

GH1 (hidden)

GH2 (hidden)

Det (hidden)

GF2 =Code.AddValue(Fields!Quantity.Value * Fieds!Cost.Value)

GF1 =Code.ShowAndResetSubTotal()

TF =Code.GrandTotal

I have the following in my Code window.

Dim Public SubTotal as Decimal

Dim Public GrandTotal as Decimal

Function ShowAndResetSubTotal() as Decimal

ShowAndResetSubTotal = SubTotal

SubTotal = 0

End Function

Function AddValue(newValue as decimal) as Decimal

SubTotal += newValue

GrandTotal += newValue

AddValue = newValue

End Function

This gives me incorrect results and I can't figure out why. Here's how it shows on my report:

Part Number Quantity Cost Regular Subtotal Method Using Custom Code Part 1 4,000 1.49 $5,947.20 Customer 1 $11,894.40 $0.00 Part 2 10 1.01 $10.07 Customer 2 $50.34 $5,947.20 Part 3 1 0.44 $0.44 Part 4 6,050 0.25 $1,530.41 Part 5 0 1.25 $0.00 Part 6 0 1.23 $0.00 Customer 3 $42,851.86 $10.07 Part 7 16,250 0.24 $3,922.59 Customer 4 $19,612.94 $1,530.85 Part 8 17,250 0.38 $6,544.82 Part 9 27,225 0.20 $5,380.20 Customer 5 $66,891.69 $3,922.59 Grand Total $141,301.23 $0.00

The issues brought up from the duplicates is shown in the "Regular Subtotal Method" column (there are 2 detail records for Customer 1-Part 1, which is why it is doubled). I can't use a distinct on the SQL query because there are other fields (not shown) on the report that are different.

As you can see, the GF1 (Customer #) shows the subtotal from the previous group, and the Table Footer (Grand Total) shows 0. Why is this?

Jarret

Hi Jarret,

The reason for seeing 0 (I think) Is that after a group ends, Reporting Services basically creates a new instance of your custom code and therefore any saved values get cleared.

I am not sure how your GF1 shows a value though... I could be wrong, but this is the experience I have had...

Regards,
Neil

|||The way I approached it...

I ordered my duplicate values... or some way of identifying that the value was not needed, and if the previous item = that item then do not add it to the total... Then for each footer call the same code and passing in the same values.

So each footer will be identical ... passing in the value and some other way of identifying if the value is unique...

Hope this helps...

Regards,
Neil

Group/Details Formatting

Hello,

I am developing a report where i have one group e.g. Dealer Name and then details of that dealer.

Dealer details can have around 10 columns and where dealer name is of around 80 chars length. I need to use 10.7in*7.1in page size.

So how i can manage the formatting for dealer group, dealer details in one page.

Thanks.

Hi Amit,

It sounds like you think you can't put the "long" group name on top of the table. Is that right?

If so, try this:

set up your group on the dealer name, as you normally would, in a table now you have your column headers for each of the detail columns, as you usually would|||

Thanks!!....I added one table row and merge the cell. In Detail section for identation i added " "/Space(3)...

Group Headers

Hi,
Is there a way to Drill down and not to show the Group header details.
Thanks
Prashtry suppressing the group header

Group Footer Overflow problem

Using Crystal Reports 7

Have a report that displays items and their amounts in the Details section

The report includes a Group section where in the Group Footer it displays the total amount of all the items from the Details section above.

We print on custom paper that lays out all the sections i.e Titles on the Page Header section, a box for the Details etc...

Problem arises when the Details section takes the entire page or most of it, leaving not enough room for the Group Footer section to print. Instead of generating a following page (i.e another page header etc) it dumps the footer on to the top of the next page - so we are left with the Group Footer (the totals) being displayed on our customs titles etc.

Therefore we end up with the subtotals being unreadable because they are printed on top of the titles of the custom page.

For some reason I can't get Crystal to regonise that this particular record flows on to another page.

I've tried using a variable to force a page break, but this either leaves big gaps in the report or some parts of the footer still overflow into the top on the next page.

Ideally I need to tell Crystal that the footer section should always print from this point on the page downwards (i.e. that start of the details section) - never above this point

I hope this is clear

Can anybody offer any other suggestions I could look into ??

CheersIn Crystal Reports 8.5, there's a property in Group Footers called PrintAtBottomOfPage. Maybe there's something similar in version 7?|||Yes Print at Bottom of Page is available in Crystal 7, however this would leave quite a significant bit of white space if say I had only a couple of detail lines.

Any other suggestions ?|||FYI

Seem to have come up with a solution that works so far:

Have created another Page Header section - so now have Page Header a and b
Page Header b is the same size as my Group Header section. Under Supress format for Page Header b have inserted:

pagenumber =1 or
InRepeatedGroupHeader = true;

Meaning that Page Header b will appear when there is no Group header section on subsequent pages

Sweet as!

Group By Table Question

Is there a way to force a group in a table to stay together on a single page? I have a group that is showing the group by on one page and the details of the group on the next page and am wondering if there is a way to force them to be together? I don't want my groups to be broken into 2 or more pages and I don't want to necessarily always force a new page break if the group will fit on the page with another group.

Thanks...

Were you able to solve this problem? My situation is even a little simpler. I just want a group to go to the next page if it won't fit on the current. I.e. If my report will contain four groups, three of which will fit on the first page the fourth of which would be broken across to the second page, I want a break to occur before the fourth group. Obviously if a group is too large for a single page, a break will occur in the middle of that group.

I know you could do this in the Oracle reporting tool, and I thought I remembered doing it in Crystal... but, I can't find the option here.
|||Hello,

There is the ability to do this at the table level, but not the group level.
However, there are a couple of workarounds possible.

The first workaround that comes to mind is to use nested Lists, where the inner list is set to be kept together on the same page, if possible. This is a checkbox at the bottom of the general tab of the List properties dialog.

Another potential resolution would be enable Repeat group headers and/or footers on each page, so that at least the continuation of the group will contain the header and/or footer on all the pages it spans. This is a checkbox at bottom of the general tab of the Grouping and Sorting property dialog.

Group By Table Question

Is there a way to force a group in a table to stay together on a single page? I have a group that is showing the group by on one page and the details of the group on the next page and am wondering if there is a way to force them to be together? I don't want my groups to be broken into 2 or more pages and I don't want to necessarily always force a new page break if the group will fit on the page with another group.

Thanks...

Were you able to solve this problem? My situation is even a little simpler. I just want a group to go to the next page if it won't fit on the current. I.e. If my report will contain four groups, three of which will fit on the first page the fourth of which would be broken across to the second page, I want a break to occur before the fourth group. Obviously if a group is too large for a single page, a break will occur in the middle of that group.

I know you could do this in the Oracle reporting tool, and I thought I remembered doing it in Crystal... but, I can't find the option here.
|||Hello,

There is the ability to do this at the table level, but not the group level.
However, there are a couple of workarounds possible.

The first workaround that comes to mind is to use nested Lists, where the inner list is set to be kept together on the same page, if possible. This is a checkbox at the bottom of the general tab of the List properties dialog.

Another potential resolution would be enable Repeat group headers and/or footers on each page, so that at least the continuation of the group will contain the header and/or footer on all the pages it spans. This is a checkbox at bottom of the general tab of the Grouping and Sorting property dialog.

GROUP BY In a GROUP BY

I dont want to get into too many details but I have a situation where I need information Aggregated, and then to have that information Aggregated again. So far I have been encapsulating all this within views but its gotten completely out of hand with too many views. So I was wondering if there was a way to make a group by within a group by?

Thanks in advance

Use derived table subqueries. This example will run in Northwind, and shows how many regions have a particular number of customers in them (eg. there are 13 regions that have one customer in them). I have highlighted the derived table, note that it is required to have an alias and its columns are required to have names or aliases too.

select CustomerCount, count(*) Regions
from
(select Region, count(*) as CustomerCount
from Customers
group by Region) CustomerCounts
group by CustomerCount
.

|||The derived table is not necessary, there are a couple of options depending on your scenarios.

If you need to display measures and subtotals on one column, you can use RollUp and Cube operators.

select region, country, sum(units) from sales group by country, region with

rollup

If you want to display them on separate columns e.g. to facilitate inter row calculation, you can use the Sql99 analytic function syntax i.e. partition by.

select region, country, sum(units) as regiontotal, sum(sum(units)) over (partition by country) as countrytotal from sales group by region, country

If you want to use a derived table, that's fine. But you have to be careful how to roll an aggregate up to the higher grain. E.g. for Count, you need to do SUM; for Avg, you need to do SUM / Count; it gets more complex for distinct aggregations.

GROUP BY and ORDER BY

hi Guys!
I am having a table
customer {
Name varchar(255),
step int,
details varchar(255)
}
I am trying to get the list of customer name with GROUP BY, and order
it using ORDER BY
SELECT Name FROM customer GROUP BY Name ORDER BY step
but cause of ORDER BY clause it fails with
Column name 'customer.step' is invalid in the ORDER BY clause because
it is not contained in either an aggregate function or the GROUP BY
clause.
any suggestions?Hi
You'll have to include a step column within a SELECT stratement (at least)
and then ORDER BY this column
<sharma.vasudev@.gmail.com> wrote in message
news:1133327048.081452.157790@.z14g2000cwz.googlegroups.com...
> hi Guys!
> I am having a table
> customer {
> Name varchar(255),
> step int,
> details varchar(255)
> }
> I am trying to get the list of customer name with GROUP BY, and order
> it using ORDER BY
> SELECT Name FROM customer GROUP BY Name ORDER BY step
> but cause of ORDER BY clause it fails with
> Column name 'customer.step' is invalid in the ORDER BY clause because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> any suggestions?
>|||Hi Vasudev,
There is a problem with Table design, looking at the query you are executing
.
Try the following query. Please note that I am using Step in select list
(for same error u got).
SELECT Distinct Name,Step FROM customer ORDER BY step
Vishal Khajuria
9886170165
IBM Bangalore
"sharma.vasudev@.gmail.com" wrote:

> hi Guys!
> I am having a table
> customer {
> Name varchar(255),
> step int,
> details varchar(255)
> }
> I am trying to get the list of customer name with GROUP BY, and order
> it using ORDER BY
> SELECT Name FROM customer GROUP BY Name ORDER BY step
> but cause of ORDER BY clause it fails with
> Column name 'customer.step' is invalid in the ORDER BY clause because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> any suggestions?
>|||ok, i did that with following query
SELECT Name, step from customer group by Name order by step
it came out with following error,
Server: Msg 8120, Level 16, State 1, Line 1
Column 'customer.step is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
so i also tried GROUP BY
SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
but as expected it returned the result which was of no use to me :(
any suggestions?|||hi Vishal,
i am really new with SQL stuff, kinda newbie, I am not sure what you
are pin-pointing to when you said 'There is a problem with Table
design' i would really appreciate if you could explain me a bit?
/dev|||To make things easy for everyone, can you just post some sample rows and
expected result?
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"hack_tick" <sharma.vasudev@.gmail.com> wrote in message
news:1133336153.369184.296940@.o13g2000cwo.googlegroups.com...
> ok, i did that with following query
> SELECT Name, step from customer group by Name order by step
> it came out with following error,
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'customer.step is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> so i also tried GROUP BY
> SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
> but as expected it returned the result which was of no use to me :(
> any suggestions?
>|||Hi Vasudev,
The point to be noticed here is that the table does not conform to rules of
normalization. You should have one more table for Name of customers.
As per your question you were trying to get list of cusotmer names by using
Group By clause --
I did not see any reason why do you need group by clause.
What I could guess is that you want distinct names of customer, that is why
I suggested query with distinct and not group by. But here also you need to
keep step in Select clause.
Please let me know if you have any more questions.
--
Vishal Khajuria
9886170165
IBM Bangalore
"hack_tick" wrote:

> hi Vishal,
> i am really new with SQL stuff, kinda newbie, I am not sure what you
> are pin-pointing to when you said 'There is a problem with Table
> design' i would really appreciate if you could explain me a bit?
> /dev
>|||hi Vishal
The Table used was just for explanation, I am having a much bigger
table with 100's of columns and many complex relationship, also the
format of table is fixed and not suppose to change :(
I shall post a complete test case with insert in a while :)|||hi guys! maybe you all can have a look at the following query!
I need to have the name of the customer displayed, but they have to be
order by column 'step'
create table customer (
name varchar(255),
step int,
details varchar(255)
)
insert into customer values('cust-1', 1, 'details-1')
insert into customer values('cust-2', 2, 'details-2')
insert into customer values('cust-2', 3, 'details-3')
insert into customer values('cust-2', 4, 'details-4')
insert into customer values('cust-3', 5, 'details-5')
insert into customer values('cust-3', 6, 'details-6')
insert into customer values('cust-3', 3, 'details-7')
insert into customer values('cust-1', 4, 'details-8')
insert into customer values('cust-4', 5, 'details-9')
insert into customer values('cust-4', 1, 'details-10')
insert into customer values('cust-5', 1, 'details-11')
insert into customer values('cust-6', 3, 'details-11')
select name from customer group by name
i have tried following queries with following error
1) SELECT Name FROM customer GROUP BY Name ORDER BY step
but cause of ORDER BY clause it fails with
Column name 'customer.step' is invalid in the ORDER BY clause because
it is not contained in either an aggregate function or the GROUP BY
clause.
2) SELECT Name, step from customer group by Name order by step
it came out with following error,
Server: Msg 8120, Level 16, State 1, Line 1
Column 'customer.step is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
so i also tried GROUP BY
SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
but as expected it returned the result which was of no use to me :(
any suggestions?
PS: Sorry for any redundant data from my earlier post, just wanted to
have a common place for your all to look at :)|||what I want is to have the List of UNIQUE Customer name, but they have
to be sorted using field 'step'

groug query

I m having 2 details table as purdtl and saledtl
i wanted to write a query by joining both details tables
purdtl table is having fields like itemcode,
batchno,qty,purno,rate,vatrate,vatamt
saledtl is having fields like itemcode, batchno,qty,saleno,irqty
i want to write a query having
purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
from both the tables
plese help me for writing above query
--
Yousuf Khan
ProgrammerHi
SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
purdtl.itemcode=saledtl.itemcode
Take a look at GROUP BY clause in the BOL
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>I m having 2 details table as purdtl and saledtl
> i wanted to write a query by joining both details tables
> purdtl table is having fields like itemcode,
> batchno,qty,purno,rate,vatrate,vatamt
> saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> i want to write a query having
> purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> from both the tables
> plese help me for writing above query
>
>
>
> --
> Yousuf Khan
> Programmer|||Can you please give me example using my tables i am not able to group it coz
both table are detail tables
]
--
Yousuf Khan
Programmer
"Uri Dimant" wrote:
> Hi
> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
> purdtl.itemcode=saledtl.itemcode
> Take a look at GROUP BY clause in the BOL
>
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
> >I m having 2 details table as purdtl and saledtl
> > i wanted to write a query by joining both details tables
> >
> > purdtl table is having fields like itemcode,
> > batchno,qty,purno,rate,vatrate,vatamt
> >
> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> >
> > i want to write a query having
> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> > from both the tables
> > plese help me for writing above query
> >
> >
> >
> >
> >
> >
> >
> > --
> > Yousuf Khan
> > Programmer
>
>|||> Can you please give me example using my tables i am not able to group it
> coz
> both table are detail tables
Yes , if you provide DDL+ sample data + expected result
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
> Can you please give me example using my tables i am not able to group it
> coz
> both table are detail tables
> ]
> --
> Yousuf Khan
> Programmer
>
> "Uri Dimant" wrote:
>> Hi
>> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
>> purdtl.itemcode=saledtl.itemcode
>> Take a look at GROUP BY clause in the BOL
>>
>>
>>
>> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
>> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>> >I m having 2 details table as purdtl and saledtl
>> > i wanted to write a query by joining both details tables
>> >
>> > purdtl table is having fields like itemcode,
>> > batchno,qty,purno,rate,vatrate,vatamt
>> >
>> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
>> >
>> > i want to write a query having
>> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
>> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
>> > from both the tables
>> > plese help me for writing above query
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > --
>> > Yousuf Khan
>> > Programmer
>>|||i have already told in my first question how i wanted the result and
i have given the fields also
--
Yousuf Khan
Programmer
"Uri Dimant" wrote:
> > Can you please give me example using my tables i am not able to group it
> > coz
> > both table are detail tables
> Yes , if you provide DDL+ sample data + expected result
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
> > Can you please give me example using my tables i am not able to group it
> > coz
> > both table are detail tables
> >
> > ]
> > --
> > Yousuf Khan
> > Programmer
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
> >> purdtl.itemcode=saledtl.itemcode
> >>
> >> Take a look at GROUP BY clause in the BOL
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> >> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
> >> >I m having 2 details table as purdtl and saledtl
> >> > i wanted to write a query by joining both details tables
> >> >
> >> > purdtl table is having fields like itemcode,
> >> > batchno,qty,purno,rate,vatrate,vatamt
> >> >
> >> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> >> >
> >> > i want to write a query having
> >> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> >> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> >> > from both the tables
> >> > plese help me for writing above query
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > --
> >> > Yousuf Khan
> >> > Programmer
> >>
> >>
> >>
>
>|||Its not enough info.
On what column you want to join? If you cant join tables directly, what is
the relationship? You also didnt post sample data, post a couple of rows in
both tables and then tell us what the result should be...
MC
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:BB081002-5B93-4BFD-BC10-15622CDE71B2@.microsoft.com...
>i have already told in my first question how i wanted the result and
> i have given the fields also
> --
> Yousuf Khan
> Programmer
>
> "Uri Dimant" wrote:
>> > Can you please give me example using my tables i am not able to group
>> > it
>> > coz
>> > both table are detail tables
>> Yes , if you provide DDL+ sample data + expected result
>>
>>
>> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
>> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
>> > Can you please give me example using my tables i am not able to group
>> > it
>> > coz
>> > both table are detail tables
>> >
>> > ]
>> > --
>> > Yousuf Khan
>> > Programmer
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
>> >> purdtl.itemcode=saledtl.itemcode
>> >>
>> >> Take a look at GROUP BY clause in the BOL
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
>> >> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>> >> >I m having 2 details table as purdtl and saledtl
>> >> > i wanted to write a query by joining both details tables
>> >> >
>> >> > purdtl table is having fields like itemcode,
>> >> > batchno,qty,purno,rate,vatrate,vatamt
>> >> >
>> >> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
>> >> >
>> >> > i want to write a query having
>> >> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
>> >> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
>> >> > from both the tables
>> >> > plese help me for writing above query
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Yousuf Khan
>> >> > Programmer
>> >>
>> >>
>> >>
>>|||Hi
look at this
Select purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
from
purdtl,saledtl
where purdtl.itemcode = saledtl.itemcode
and purdtl.batchno = saledtl.batchno
group by
purdtl.itemcode,purdtl.batchno,saledtl.itemcode,saledtl.batchno|||And you try so hard:( :)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O5a0ykFZGHA.4836@.TK2MSFTNGP05.phx.gbl...
>> Can you please give me example using my tables i am not able to group it
>> coz
>> both table are detail tables
> Yes , if you provide DDL+ sample data + expected result
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
>> Can you please give me example using my tables i am not able to group it
>> coz
>> both table are detail tables
>> ]
>> --
>> Yousuf Khan
>> Programmer
>>
>> "Uri Dimant" wrote:
>> Hi
>> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
>> purdtl.itemcode=saledtl.itemcode
>> Take a look at GROUP BY clause in the BOL
>>
>>
>>
>> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
>> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>> >I m having 2 details table as purdtl and saledtl
>> > i wanted to write a query by joining both details tables
>> >
>> > purdtl table is having fields like itemcode,
>> > batchno,qty,purno,rate,vatrate,vatamt
>> >
>> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
>> >
>> > i want to write a query having
>> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
>> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
>> > from both the tables
>> > plese help me for writing above query
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > --
>> > Yousuf Khan
>> > Programmer
>>
>|||both table are having same type of data
itemcode, batchno, qty, rate and their can be repeatation of same itemcode
and batchno
table purdtl is detail table of items purchase and table saledtl is detail
table of sale items
i want the result the total items purchased from purdtl whoose vatrate=12 and
and group on same itemcode and batchno and the total sale of that item from
saledtl their can be multiple records of same itemcode and batchno the
records should be group on itemcode and batchno
Yousuf Khan
Programmer
"MC" wrote:
> Its not enough info.
> On what column you want to join? If you cant join tables directly, what is
> the relationship? You also didnt post sample data, post a couple of rows in
> both tables and then tell us what the result should be...
>
> MC
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:BB081002-5B93-4BFD-BC10-15622CDE71B2@.microsoft.com...
> >i have already told in my first question how i wanted the result and
> > i have given the fields also
> > --
> > Yousuf Khan
> > Programmer
> >
> >
> > "Uri Dimant" wrote:
> >
> >> > Can you please give me example using my tables i am not able to group
> >> > it
> >> > coz
> >> > both table are detail tables
> >>
> >> Yes , if you provide DDL+ sample data + expected result
> >>
> >>
> >>
> >>
> >> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> >> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
> >> > Can you please give me example using my tables i am not able to group
> >> > it
> >> > coz
> >> > both table are detail tables
> >> >
> >> > ]
> >> > --
> >> > Yousuf Khan
> >> > Programmer
> >> >
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Hi
> >> >> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
> >> >> purdtl.itemcode=saledtl.itemcode
> >> >>
> >> >> Take a look at GROUP BY clause in the BOL
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> >> >> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
> >> >> >I m having 2 details table as purdtl and saledtl
> >> >> > i wanted to write a query by joining both details tables
> >> >> >
> >> >> > purdtl table is having fields like itemcode,
> >> >> > batchno,qty,purno,rate,vatrate,vatamt
> >> >> >
> >> >> > saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> >> >> >
> >> >> > i want to write a query having
> >> >> > purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> >> >> > saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> >> >> > from both the tables
> >> >> > plese help me for writing above query
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Yousuf Khan
> >> >> > Programmer
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

groug query

I m having 2 details table as purdtl and saledtl
i wanted to write a query by joining both details tables
purdtl table is having fields like itemcode,
batchno,qty,purno,rate,vatrate,vatamt
saledtl is having fields like itemcode, batchno,qty,saleno,irqty
i want to write a query having
purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
from both the tables
plese help me for writing above query
Yousuf Khan
ProgrammerHi
SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
purdtl.itemcode=saledtl.itemcode
Take a look at GROUP BY clause in the BOL
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>I m having 2 details table as purdtl and saledtl
> i wanted to write a query by joining both details tables
> purdtl table is having fields like itemcode,
> batchno,qty,purno,rate,vatrate,vatamt
> saledtl is having fields like itemcode, batchno,qty,saleno,irqty
> i want to write a query having
> purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
> saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
> from both the tables
> plese help me for writing above query
>
>
>
> --
> Yousuf Khan
> Programmer|||Can you please give me example using my tables i am not able to group it coz
both table are detail tables
]
--
Yousuf Khan
Programmer
"Uri Dimant" wrote:

> Hi
> SELECT <columns list> FROM purdtl INNER JOIN saledtl ON
> purdtl.itemcode=saledtl.itemcode
> Take a look at GROUP BY clause in the BOL
>
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:D8A2230B-02D4-4245-8924-BC3FBF93365A@.microsoft.com...
>
>|||> Can you please give me example using my tables i am not able to group it
> coz
> both table are detail tables
Yes , if you provide DDL+ sample data + expected result
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...[vbcol=seagreen]
> Can you please give me example using my tables i am not able to group it
> coz
> both table are detail tables
> ]
> --
> Yousuf Khan
> Programmer
>
> "Uri Dimant" wrote:
>|||i have already told in my first question how i wanted the result and
i have given the fields also
--
Yousuf Khan
Programmer
"Uri Dimant" wrote:

> Yes , if you provide DDL+ sample data + expected result
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
>
>|||Its not enough info.
On what column you want to join? If you cant join tables directly, what is
the relationship? You also didnt post sample data, post a couple of rows in
both tables and then tell us what the result should be...
MC
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:BB081002-5B93-4BFD-BC10-15622CDE71B2@.microsoft.com...[vbcol=seagreen]
>i have already told in my first question how i wanted the result and
> i have given the fields also
> --
> Yousuf Khan
> Programmer
>
> "Uri Dimant" wrote:
>|||Hi
look at this
Select purdtl.itemcode,purdtl.batchno,sum(purdtl.qty),sum(purdtl.rate),
saledtl.itemcode,saledtl.batchno,sum(saledtl.qty),sum(saledtl.irqty)
from
purdtl,saledtl
where purdtl.itemcode = saledtl.itemcode
and purdtl.batchno = saledtl.batchno
group by
purdtl.itemcode,purdtl.batchno,saledtl.itemcode,saledtl.batchno|||And you try so hard
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O5a0ykFZGHA.4836@.TK2MSFTNGP05.phx.gbl...
> Yes , if you provide DDL+ sample data + expected result
>
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:5987B8B1-441C-468F-964D-48079E5903B0@.microsoft.com...
>|||both table are having same type of data
itemcode, batchno, qty, rate and their can be repeatation of same itemcode
and batchno
table purdtl is detail table of items purchase and table saledtl is detail
table of sale items
i want the result the total items purchased from purdtl whoose vatrate=12 an
d
and group on same itemcode and batchno and the total sale of that item from
saledtl their can be multiple records of same itemcode and batchno the
records should be group on itemcode and batchno
Yousuf Khan
Programmer
"MC" wrote:

> Its not enough info.
> On what column you want to join? If you cant join tables directly, what is
> the relationship? You also didnt post sample data, post a couple of rows i
n
> both tables and then tell us what the result should be...
>
> MC
>
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:BB081002-5B93-4BFD-BC10-15622CDE71B2@.microsoft.com...
>
>