Could you please provide some examples with tables structure, data and needed result?

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

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.

thanks I did manage to do it

but now i have a still bigger db 3 millon+ records how do i optimize this query ??

Can you please provide EXPLAIN for your query which you would like to optimize?

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

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.

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

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 )

g