counting in my sql XxY

MySQL & MariaDB Other MySQL® Questions
1

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.

2

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

3

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

4

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.

5
[B]delta2cain1 wrote on Mon, 12 May 2008 03:17[/B]

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

6

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 |
±—±-------------------±---------------------±------±- -------------±------------±--------±-----±--------±---- --------------------------------------+

  1. show create table profile

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

7
[B]delta2cain1 wrote on Mon, 12 May 2008 19:38[/B]
  1. show create table profile

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

You should do ‘SHOW CREATE TABLE’ for your actual table )

8

g