Pivot Query in

------=_NextPart_000_01C7_01CAE6E6.ECB175C0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

I am working on Java-HTML-MS Access Backend database.

Now office is removing all unlicensed softwares and they are removing
microsoft applications also and hence the necessity to shift from MS Access
to MySQL has arised.

In MS Access I used to generate one pivot query which gets the following
report from the database which contains the following fields:

Date, ProjectCode Building, Number of Copies



I want to get a Connsolidate Report of

Project Code R&D STP

1007304----------04-------04

(Group by Project Code)(Sumtotal Building wise).



I have tried to execute the following code :

select pcode, building, sum(ncopies) from request group by pcode,building



It is giving the following repott

1007304--R&D--04

1007304-STP--05

Here there is a repetition of pcode,



How can I avoid this. I want to get the Building Name on top as Column Names
and bottom I should get the Count.



--
With regards,
VR Venugopal Rao
91-90526-07186



____________________________________________________________ __________________

DISCLAIMER

The information contained in this e-mail message and/or attachments to it may
contain confidential or privileged information. If you are not the intended
recipient, any dissemination, use, review, distribution, printing or copying
of the information contained in this e-mail message and/or attachments to it
are strictly prohibited. If you have received this communication in error,
please notify us by reply e-mail or directly to netsupport@cmcltd.com or
telephone and immediately and permanently delete the message and any
attachments. Thank you.


____________________________________________________________ __________________

This email has been scrubbed for your protection by SecureMX.
For more information visit http://securemx.in
____________________________________________________________ __________________


------=_NextPart_000_01C7_01CAE6E6.ECB175C0--

Re: Pivot Query in

On Wed, 2010-04-28 at 15:24 +0530, VR Venugopal Rao wrote:
> I am working on Java-HTML-MS Access Backend database.
>
> Now office is removing all unlicensed softwares and they are removing
> microsoft applications also and hence the necessity to shift from MS Access
> to MySQL has arised.
>
> In MS Access I used to generate one pivot query which gets the following
> report from the database which contains the following fields:
>
> Date, ProjectCode Building, Number of Copies
>
>
>
> I want to get a Connsolidate Report of
>
> Project Code R&D STP
>
> 1007304----------04-------04
>
> (Group by Project Code)(Sumtotal Building wise).
>
>
>
> I have tried to execute the following code :
>
> select pcode, building, sum(ncopies) from request group by pcode,building
>
>
>
> It is giving the following repott
>
> 1007304--R&D--04
>
> 1007304-STP--05
>
> Here there is a repetition of pcode,
>
>
>
> How can I avoid this.

Don't use unlicenced commercial software in the first place?

> I want to get the Building Name on top as Column Names
> and bottom I should get the Count.
>

Unlike MS Access MySQL does not directly support 'pivot queries', the
portable equivalent is a cross-tab query. In order to allow for the
addition and removal of departments you'll want to dynamically generate
the query each time in a client application or a stored procedure

Crosstab queries in MySQL
http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-m ysql.html

Crosstab queries and their native support in MS Access
http://www.paragoncorporation.com/ArticleDetail.aspx?Article ID=25

HTH

Nigel


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Pivot Query in

[snip]
Date, ProjectCode Building, Number of Copies

I want to get a Connsolidate Report of=20

Project Code R&D STP

1007304----------04-------04

(Group by Project Code)(Sumtotal Building wise).

I have tried to execute the following code :

select pcode, building, sum(ncopies) from request group by
pcode,building

It is giving the following repott

1007304--R&D--04

1007304-STP--05

Here there is a repetition of pcode,

How can I avoid this. I want to get the Building Name on top as Column
Names
and bottom I should get the Count.
[/snip]

We would need to see some of the raw data to help you but this older
article may point you in the right direction with crosstab (pivot)
queries;

http://www.evolt.org/node/26896


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Pivot Query in

--------------070106040401010301030707
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>In MS Access I used to generate one pivot query which gets the following
>report from the database which contains the following fields:

See "Pivot tables" at http://www.artfulsoftware.com/queries.php .

PB

-----

Jay Blanchard wrote:
> [snip]
> Date, ProjectCode Building, Number of Copies
>
> I want to get a Connsolidate Report of
>
> Project Code R&D STP
>
> 1007304----------04-------04
>
> (Group by Project Code)(Sumtotal Building wise).
>
> I have tried to execute the following code :
>
> select pcode, building, sum(ncopies) from request group by
> pcode,building
>
> It is giving the following repott
>
> 1007304--R&D--04
>
> 1007304-STP--05
>
> Here there is a repetition of pcode,
>
> How can I avoid this. I want to get the Building Name on top as Column
> Names
> and bottom I should get the Count.
> [/snip]
>
> We would need to see some of the raw data to help you but this older
> article may point you in the right direction with crosstab (pivot)
> queries;
>
> http://www.evolt.org/node/26896
>
>
>
> ------------------------------------------------------------ ------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.437 / Virus Database: 271.1.1/2840 - Release Date: 04/28/10 06:27:00
>
>

--------------070106040401010301030707--

Pivot Query in MySQL

Thanks Jay, I got the solution.
Thanks for all the members responding.

With warm regards,
VR Venugopal Rao

-----Original Message-----
From: Jay Blanchard [mailto:jblanchard@pocket.com]
Sent: 28 April 2010 16:58
To: VR Venugopal Rao; MySQL
Subject: RE: Pivot Query in

[snip]
Date, ProjectCode Building, Number of Copies

I want to get a Connsolidate Report of

Project Code R&D STP

1007304----------04-------04

(Group by Project Code)(Sumtotal Building wise).

I have tried to execute the following code :

select pcode, building, sum(ncopies) from request group by
pcode,building

It is giving the following repott

1007304--R&D--04

1007304-STP--05

Here there is a repetition of pcode,

How can I avoid this. I want to get the Building Name on top as Column
Names
and bottom I should get the Count.
[/snip]

We would need to see some of the raw data to help you but this older
article may point you in the right direction with crosstab (pivot)
queries;

http://www.evolt.org/node/26896



____________________________________________________________ __________________

DISCLAIMER

The information contained in this e-mail message and/or attachments to it may
contain confidential or privileged information. If you are not the intended
recipient, any dissemination, use, review, distribution, printing or copying
of the information contained in this e-mail message and/or attachments to it
are strictly prohibited. If you have received this communication in error,
please notify us by reply e-mail or directly to netsupport@cmcltd.com or
telephone and immediately and permanently delete the message and any
attachments. Thank you.


____________________________________________________________ __________________

This email has been scrubbed for your protection by SecureMX.
For more information visit http://securemx.in
____________________________________________________________ __________________


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

How to Get Running Totals.

--0-2112139497-1272861457=:66935
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

I am trying to get running totals for the following:
Project Code---R&D---STP--RunningTotal
1007303----------04-------04---8
1007304----------04-------04---16
=A0
I tried to follow the examples available on the site but it is giving error=
s.
I am using java as a middleware and mysql as back end.
I am trying ot use backend query for getting the results.
response with examples will be more helpful.
With regards,
VR Venugopal Rao
=A0



--- On Wed, 28/4/10, Jay Blanchard wrote:


From: Jay Blanchard
Subject: RE: Pivot Query in
To: "VR Venugopal Rao" , "MySQL" ..mysql.com>
Date: Wednesday, 28 April, 2010, 4:57 PM


[snip]
Date, ProjectCode Building, Number of Copies

I want to get a Connsolidate Report of=20

Project Code  =A0R&D  =A0STP

1007304----------04-------04

(Group by Project Code)(Sumtotal Building wise).

I have tried to execute the following code :

select pcode, building, sum(ncopies) from request group by
pcode,building

It is giving the following repott

1007304--R&D--04

1007304-STP--05

Here there is a repetition of pcode,

How can I avoid this. I want to get the Building Name on top as Column
Names
and bottom I should get the Count.
[/snip]

We would need to see some of the raw data to help you but this older
article may point you in the right direction with crosstab (pivot)
queries;

http://www.evolt.org/node/26896


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:=A0 =A0 http://lists.mysql.com/mysql?unsub=3Dvr_vrao@yahoo.c=
o.in


--0-2112139497-1272861457=:66935--

Re: How to Get Running Totals.

--------------060100050308020702030109
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>I am trying to get running totals

See "Running sum" at http://www.artfulsoftware.com/infotree/queries.php.

PB

-----

Venugopal Rao wrote:
> I am trying to get running totals for the following:
> Project Code---R&D---STP--RunningTotal
> 1007303----------04-------04---8
> 1007304----------04-------04---16
>
> I tried to follow the examples available on the site but it is giving errors.
> I am using java as a middleware and mysql as back end.
> I am trying ot use backend query for getting the results.
> response with examples will be more helpful.
> With regards,
> VR Venugopal Rao
>
>
>
>
> --- On Wed, 28/4/10, Jay Blanchard wrote:
>
>
> From: Jay Blanchard
> Subject: RE: Pivot Query in
> To: "VR Venugopal Rao" , "MySQL"
> Date: Wednesday, 28 April, 2010, 4:57 PM
>
>
> [snip]
> Date, ProjectCode Building, Number of Copies
>
> I want to get a Connsolidate Report of
>
> Project Code R&D STP
>
> 1007304----------04-------04
>
> (Group by Project Code)(Sumtotal Building wise).
>
> I have tried to execute the following code :
>
> select pcode, building, sum(ncopies) from request group by
> pcode,building
>
> It is giving the following repott
>
> 1007304--R&D--04
>
> 1007304-STP--05
>
> Here there is a repetition of pcode,
>
> How can I avoid this. I want to get the Building Name on top as Column
> Names
> and bottom I should get the Count.
> [/snip]
>
> We would need to see some of the raw data to help you but this older
> article may point you in the right direction with crosstab (pivot)
> queries;
>
> http://www.evolt.org/node/26896
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vr_vrao@yahoo.co.in
>
>
>
>
>
> ------------------------------------------------------------ ------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.437 / Virus Database: 271.1.1/2849 - Release Date: 05/02/10 06:27:00
>
>

--------------060100050308020702030109--