Members Members   Search Search   Help Help   Register Register   Login Login   Home Home


Home » Performance » MySQL » counting in my sql XxY
counting in my sql XxY [message #3057] Fri, 09 May 2008 05:36 Go to next message
delta2cain1 is currently offline delta2cain1
Messages: 7
Registered: May 2008
Junior Member
Hi I have a table with 2 columns Name (variable type) and CODE (variable type)

I need to retrieve a result in the following format

NAME -->
CODE
| FREQUENCY
|
V

The number of names may vary
so also the code

I Need to write a query in MySQL

Please point me in the right direction.
Report message to a moderator
Re: counting in my sql XxY [message #3060 is a reply to message #3057 ] Sat, 10 May 2008 12:22 Go to previous messageGo to next message
debug is currently offline debug
Messages: 122
Registered: March 2008
Senior Member
Could you please provide some examples with tables structure, data and needed result?

Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Report message to a moderator
Re: counting in my sql XxY [message #3063 is a reply to message #3057 ] Mon, 12 May 2008 03:17 Go to previous messageGo to next message
delta2cain1 is currently offline delta2cain1
Messages: 7
Registered: May 2008
Junior Member

SELECT ABC , count(*) AS "Count", XYZ FROM profile WHERE XYZ IN (SELECT DISTINCT a.XYZ FROM profile a) GROUP BY ABC,XYZ;


gives me a table of type

ABC COUNT XYZ
a 23 z
b 45 z
c 65 z
...

a 76 y
b 56 y
...
...

a 45 x
b 46 x

etc etc

now i neet it to be put in the format

XYZ z y x .....
ABC
a 23 76 45 ...
b 45 56 46 ...
c 65 ......
. . . .
. . . .


Help Me plz

Report message to a moderator
Re: counting in my sql XxY [message #3064 is a reply to message #3063 ] Mon, 12 May 2008 03:40 Go to previous messageGo to next message
debug is currently offline debug
Messages: 122
Registered: March 2008
Senior Member
Unfortunately there is no way to do it via just one query. You should parse output of query using some external script. If you do select with "\G" at the end instead of ";", you will receive data in vertical format, something like this:
*************************** 1. row ***************************
user_id: 1
quiz_id: 1
activity_date: 2008-02-02 00:00:00
*************************** 2. row ***************************
user_id: 1
quiz_id: 3
activity_date: 2008-02-02 00:00:00
*************************** 3. row ***************************
user_id: 1
quiz_id: 9
activity_date: 2008-02-02 00:00:00
And it will be not difficult to process such output using some perl script to build a table with swapped rows<->columns.

Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Report message to a moderator
Re: counting in my sql XxY [message #3067 is a reply to message #3064 ] Mon, 12 May 2008 06:35 Go to previous messageGo to next message
delta2cain1 is currently offline delta2cain1
Messages: 7
Registered: May 2008
Junior Member
thanks I did manage to do it

but now i have a still bigger db 3 millon+ records how do i optimize this query ??
Report message to a moderator
Re: counting in my sql XxY [message #3068 is a reply to message #3067 ] Mon, 12 May 2008 06:59 Go to previous messageGo to next message
debug is currently offline debug
Messages: 122
Registered: March 2008
Senior Member
Can you please provide EXPLAIN for your query which you would like to optimize?

Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Report message to a moderator
Re: counting in my sql XxY [message #3069 is a reply to message #3063 ] Mon, 12 May 2008 08:10 Go to previous messageGo to next message
delta2cain1 is currently offline delta2cain1
Messages: 7
Registered: May 2008
Junior Member
delta2cain1 wrote on Mon, 12 May 2008 03:17


SELECT ABC , count(*) AS "Count", XYZ FROM profile WHERE XYZ IN (SELECT DISTINCT a.XYZ FROM profile a) GROUP BY ABC,XYZ;




this query it self
Report message to a moderator
Re: counting in my sql XxY [message #3070 is a reply to message #3069 ] Mon, 12 May 2008 08:11 Go to previous messageGo to next message
debug is currently offline debug
Messages: 122
Registered: March 2008
Senior Member
Please run:
1) EXPAIN SELECT ABC , count(*) AS "Count", XYZ FROM profile WHERE XYZ IN (SELECT DISTINCT a.XYZ FROM profile a) GROUP BY ABC,XYZ
2) show create table profile

and post output here.

Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Report message to a moderator
Re: counting in my sql XxY [message #3071 is a reply to message #3057 ] Mon, 12 May 2008 08:38 Go to previous messageGo to next message
delta2cain1 is currently offline delta2cain1
Messages: 7
Registered: May 2008
Junior Member
the actual query was

EXPLAIN SELECT Errorcode , count(*) AS "Count", ClientName FROM customer_profile_SMM WHERE ClientName IN (SELECT DISTINCT a.ClientName FROM customer_profile_SMM a) GROUP BY Errorcode,ClientName;

1) EXPAIN SELECT ABC , count(*) AS "Count", XYZ FROM profile WHERE XYZ IN (SELECT DISTINCT a.XYZ FROM profile a) GROUP BY ABC,XYZ


+----+--------------------+----------------------+-------+-- -------------+-------------+---------+------+---------+----- --------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------------------+-------+-- -------------+-------------+---------+------+---------+----- --------------------------------------+
| 1 | PRIMARY | customer_profile_SMM | index | NULL | Errorcode_2 | 65 | NULL | 3785102 | Usingwhere; Using index |
| 2 | DEPENDENT SUBQUERY | a | index | NULL | Errorcode_2 | 65 | NULL | 3785102 | Usingwhere; Using index; Using temporary |
+----+--------------------+----------------------+-------+-- -------------+-------------+---------+------+---------+----- --------------------------------------+


2) show create table profile

ERROR 1146 (42S02): Table 'Data_base.profile' doesn't exist

[Updated on: Mon, 12 May 2008 08:40]

Report message to a moderator
Re: counting in my sql XxY [message #3072 is a reply to message #3071 ] Mon, 12 May 2008 08:41 Go to previous messageGo to next message
debug is currently offline debug
Messages: 122
Registered: March 2008
Senior Member
delta2cain1 wrote on Mon, 12 May 2008 19:38


2) show create table profile

ERROR 1146 (42S02): Table 'Data_base.profile' doesn't exist


You should do 'SHOW CREATE TABLE' for your actual table Smile

Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Report message to a moderator
Re: counting in my sql XxY [message #3073 is a reply to message #3057 ] Mon, 12 May 2008 09:45 Go to previous message
delta2cain1 is currently offline delta2cain1
Messages: 7
Registered: May 2008
Junior Member
g

[Updated on: Thu, 15 May 2008 02:21]

Report message to a moderator
Previous Topic:Large table
Next Topic:Create merge table using unpriviledge user
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]


Current Time: Fri Dec 5 11:29:16 EST 2008

Total time taken to generate the page: 0.01442 seconds
.:: Contact :: Home :: MySQL Support by Percona.com ::.

Powered by: FUDforum 2.7.5.
Copyright ©2001-2006 FUD Forum Bulletin Board Software

MySQL Performance | Forum authority Badge