Announcement

Announcement Module
Collapse
No announcement yet.

counting in my sql XxY

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • counting in my sql XxY

    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?

    Comment


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

      Comment


      • #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.

        Comment


        • #5
          thanks I did manage to do it

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

          Comment


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

            Comment


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

              Comment


              • #8
                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.

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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 )

                    Comment


                    • #11
                      g

                      Comment

                      Working...
                      X