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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>