Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2012
    Posts
    19

    Unanswered: Multiple SQL Query Output

    I am wondering if it is possible to have two queries outputs with varying row/column counts and different columns in the same extraction.

    I have around 7 different SQL queries needed for weekly reporting that would be ideal to have in one report for ease of use.

    For an incredibly simple example

    Query #1
    Code:
    SELECT DISTINCT student.trainer, COUNT(student.name) AS Count
    FROM student
    GROUP BY student.trainer
    Query #2
    Code:
    SELECT student.trainer, student.name, student.code
    FROM student
    So the output would simply stack the queries with a space in between. Like below. Essentially I have complex count functions and then need to be able to list more detailed information below.

    trainer | Count |
    ===============
    John | 1 |
    Jackie | 2 |

    trainer | name | code |
    ============================
    John | J.smith | A12 |
    Jackie | K.black | B17 |
    Jackie | P.Piper | Z101 |

    I know this can be done through other means, however can it be done through a simple code and push into a CSV. Or is this only possible through other means.

    Thanks!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Which SQL client are you using? Exporting to CSV is done by the SQL client, not by "MySQL" itself. Different clients have different features.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by shammat View Post
    Exporting to CSV is done by the SQL client, not by "MySQL" itself.
    Ummm, The MySQL Manual would take issue with that!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2012
    Posts
    19
    I am using the php mysql client versino 5.5.22 that is linked to my CRM, SugarCRM.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You ought to be able to create the CSV files on the MySQL server by executing a SELECT statement.

    It would probably be easier/faster/more flexible to produce them using something like MyPHPAdmin if you have it.

    I haven't used Sugar CRM in a long time, but I vaguely recall that you could execute (and maybe save the results from) SQL queries within the Sugar CRM package.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jun 2012
    Posts
    19
    Hey Pat, yea I can create csv files, save SQL queries etc but its just trying to combine two completly unrelated SQL queries into the one output to streamlines processes that I was hoping to do.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't understand how combining to unrelated heaps of information would help.

    Are you looking to combine them side by side, comingled, or one after another?

    What real world purpose does combining the information from your queries help you to solve? The data can certainly be combined, in many different ways.

    Without understanding more about your underlying problem I can't figure out which of the ways is applicable or helpful to solving your problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jun 2012
    Posts
    19
    Within my current organisation we have 5 division heads who need to report weekly on their teams performances, across a wide range of KPIs. Due to how diverse the data is I have had to write around 7-12 different queries for each division to be able to report on their KPIs

    The real world application of combining these queries is one easy they can run just one report instead of numerous different ones.

    I am looking to combine them both side by side or one after the other. Some devisions will benefit from side by side, as the output reports are literally a single row with multiple columns, whereas others will have to be one after another due to the diversity in rows and columns! Appreciate your thoughts on this

  9. #9
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I would do this in an application where you output the contents of each query in CSV format or your use MySQL to generate the 7-12 different queries to different files and then you merge these into a single file.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •