SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Output query in MySQL

    Hello everyone.

    I have this query and this output:

    Code:
    SELECT * , 
    COUNT(STATE) AS strSTATE, 
    COUNT(TYPESTATE) AS strTYPESTATE 
    FROM 
    tbl_MYSQL 
    WHERE STATE = 'ABSENT' 
    AND DATE = '2008-04-29' 
    GROUP BY STATE, 
    TYPESTATE, 
    DESCR 
    ORDER BY DESCR ASC
    HTML Code:
    AAA BBB-ZONE CA            9168381000  Expectancy 1  29/04/2008
    AAA BBB-ZO CA-UOD MONTAG-1 9168381620  Disease    1  29/04/2008
    AAA BBB-ZO CA-UOD MONTAG-9 9168381629  Holiday    1  29/04/2008
    AAA BBB-ZO CA-UOD DIENSTAG 9168381650  Permit     1  29/04/2008
    AAA BBB-ZO CA-UOD DIENSTAG 9168381650  Holiday    1  29/04/2008
    To have this output?
    Can you help me?

    HTML Code:
    AAA BBB-ZONE CA       9168381000  Expectancy 1  29/04/2008
                                      Disease    1  29/04/2008
                                      Holiday    1  29/04/2008
                                      Permit     1  29/04/2008
                                      Holiday    1  29/04/2008

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no

    do that in your application layer, do not even attempt to do that with sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you have to take care of formatting your output in your host langauge.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Its not possible with SQL ?

    Maybe I explained wrong the problem...

    No possibility of obtaining this :

    HTML Code:
    AAA BBB-ZONE CA 9168381000
    from

    HTML Code:
    AAA BBB-ZONE CA            9168381000
    AAA BBB-ZO CA-UOD MONTAG-1 9168381620
    AAA BBB-ZO CA-UOD MONTAG-9 9168381629
    AAA BBB-ZO CA-UOD DIENSTAG 9168381650
    AAA BBB-ZO CA-UOD DIENSTAG 9168381650
    Is not a group of similar lines?


    ???

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ?????

    if you can explain how you got that one line result please???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    ?????

    if you can explain how you got that one line result please???
    this is output (... in theory ... ), it's possible with SQL ?

    HTML Code:
    DESCRIPTION     NUMBER     Expectancy    Disease    Holiday   Permit  DATE
    AAA BBB-ZONE CA 9168381000   1            1            2        1     29/04/2008
    Last edited by Miguel61; Apr 30, 2008 at 03:18.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, it's gotta be possible, but since i don't know what you're doing, i can't tell you how
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yeah, it's gotta be possible, but since i don't know what you're doing, i can't tell you how
    thanks to positive reply.

    I explain ( forget the old my query... ok? ):

    The table tbl_mysql contain this row:

    HTML Code:
    DESCRIPTION		   NUMBER	TYPESTATE	DATE
    
    AAA BBB-ZONE CA            9168381000  Expectancy   29/04/2008
    AAA BBB-ZO CA-UOD MONTAG-1 9168381620  Disease      29/04/2008
    AAA BBB-ZO CA-UOD MONTAG-9 9168381629  Holiday      29/04/2008
    AAA BBB-ZO CA-UOD DIENSTAG 9168381650  Permit       29/04/2008
    AAA BBB-ZO CA-UOD DIENSTAG 9168381650  Holiday      29/04/2008
    I would extract with a single query this output:

    HTML Code:
    DESCRIPTION     NUMBER     Expectancy    Disease    Holiday   Permit  DATE
    AAA BBB-ZONE CA 9168381000   1            1            2        1     29/04/2008

    Can you help me ?
    Regards

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, sorry, i cannot
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, sorry, i cannot
    You can not because I ask impossible query ?

    I not explain well the problem ?

    You are "SQL Consultant - Team Leader" , I thought different reply for you... one suggestion, one modify in the table mysql to get output...

    I'am only "SitePoint Enthusiast".... thanks however for your replies....
    Last edited by Miguel61; Apr 30, 2008 at 07:32.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i cannot help you because i do not understand your data, and i do not understand how the many descriptions can be related to each other
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i cannot help you because i do not understand your data, and i do not understand how the many descriptions can be related to each other
    OK, I understand.

    I modify my table mysql:

    Code:
    ID_DESCRIPTION	DESCRIPTION		   NUMBER	STATE       TYPESTATE	 DATE
    
    10              AAA BBB-ZONE CA            9168381000   ABSENT      Expectancy   29/04/2008
    10              AAA BBB-ZO CA-UOD MONTAG-1 9168381620   ABSENT      Disease      29/04/2008
    10              AAA BBB-ZO CA-UOD MONTAG-9 9168381629   ABSENT      Holiday      29/04/2008
    10              AAA BBB-ZO CA-UOD DIENSTAG 9168381650   ABSENT      Permit       29/04/2008
    10              AAA BBB-ZO CA-UOD DIENSTAG 9168381650   ABSENT      Holiday      29/04/2008
    And execute this query:

    Code:
    SELECT * , 
    COUNT(TYPESTATE) AS strTYPESTATE 
    FROM 
    tbl_MYSQL 
    WHERE STATE = 'ABSENT' 
    AND DATE = '2008-04-29' 
    GROUP BY 
    ID_DESCRIPTION, 
    TYPESTATE, 
    ORDER BY 
    DESCRIPTION ASC
    The output:

    Code:
    10 === > Expectancy === > 1 === > 2008-04-29
    10 === > Disease === > 1 === > 2008-04-29
    10 === > Holiday === > 2 === > 2008-04-29
    10 === > Permit === > 1 === > 2008-04-29
    I would this other output:

    Code:
    ID_DESCRIPTION     Expectancy    Disease    Holiday   Permit  DATE
    10                 1             1          2         1       29/04/2008
    Can you help me ?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, now i can help you -- you answered my question ("how the many descriptions can be related to each other") by revealing ID_DESCRIPTION, which ties them all together, which you had previously been hiding


    Code:
    SELECT id_description	
         , MIN(description) min_descr
         , COUNT(CASE WHEN typestate = 'Expectancy' 
                      THEN 'yes' ELSE NULL END) AS Expectancy
         , COUNT(CASE WHEN typestate = 'Disease'    
                      THEN 'yes' ELSE NULL END) AS Disease
         , COUNT(CASE WHEN typestate = 'Holiday'    
                      THEN 'yes' ELSE NULL END) AS Holiday
         , COUNT(CASE WHEN typestate = 'Permit'     
                      THEN 'yes' ELSE NULL END) AS Permit
      FROM tbl_MYSQL 
     WHERE state = 'ABSENT' 
       AND `date` = '2008-04-29' 
    GROUP 
        BY id_description	
    ORDER 
        BY min_descr
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Many, many thanks You are GREAT "SQL Consultant - Team Leader"...

    I have other question:

    Can I have the same query this output ( I add Total Rows and Total Columns... ) : ?

    Code:
    ID_DESCRIPTION     Expectancy    Disease    Holiday   Permit  DATE           TOTAL
    10                 1             1          2         1       29/04/2008     5
    11                 2             0          2         3       29/04/2008     7
    Total              3             1          4         4       29/04/2008     12

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    for the total going across the row, add COUNT()* AS rowtotal to the SELECT clause

    for column totals, add WITH ROLLUP to the GROUP BY clause, and remove the ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Sorry... I not understand...

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, try the WITH ROLLUP part first

    add WITH ROLLUP to the GROUP BY clause, and remove the ORDER BY clause

    see the manual if you're not sure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thanks x your suggestion... I try ....


Bookmarks

Posting Permissions

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