SitePoint Sponsor

User Tag List

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

    Problem with query RollUp

    Hi there, I need your appreciated help.

    My incorrect output:
    Code:
    DRT	P > 16.5 	P <= 16.5 	tot ve	tot an	tot pi	tot	% pi	% tr
    I	115		91		206	99	25	124	20	60
    M	212		782		994	632	150	782	19	79
    O	301		891		1192	544	345	889	39	75
    S	74		101		175	87	34	121	28	69
    S	702		1865		2567	1362	554	1916	29	75
    I need this output:
    Code:
    DRT	P > 16.5 	P <= 16.5 	tot ve	tot an	tot pi	tot	% pi	% tr
    I	115		91		206	99	25	124	20	60
    M	212		782		994	632	150	782	19	79
    O	301		891		1192	544	345	889	39	75
    S	74		101		175	87	34	121	28	69
    Tot	702		1865		2567	1362	554	1916	29	75
    In the last row don't have the field name `Tot` but `S` (last DRT) why?
    Can you help me?

    My query:
    Code:
    SELECT 	
              COALESCE(Left(Zn,2),'Tot') DRT
           , `P > 16.5`
           , `P <= 16.5`
           , `tot ve`
           , `tot an`
           , `tot pi`
           , (`tot an`+`tot pi`) `tot`
           , FORMAT((`tot pi`/(`tot an`+`tot pi`))*100,0) `% pi`
           , FORMAT(((`tot an` + `tot pi`)/`tot ve`)*100,0) `% tr`
           
      FROM (SELECT
        Zn
      , COUNT(*) 'tot ve' 
      , SUM(IF(p > 16.5, 1, 0)) 'P > 16.5'
      , SUM(IF(p <= 16.5, 1, 0)) 'P <= 16.5'
      , SUM(IF(AN='S', 1, 0)) 'tot an'
      , SUM(IF(PI='S', 1, 0)) 'tot pi'
      , '% pi'
      , '% tr'
      
            FROM tbl_q
            WHERE 1 
            GROUP BY Left(Zn,2) WITH ROLLUP) x;
    
    Time: 0.016ms

  2. #2
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I need relax....

    Code:
    SELECT 	
             COALESCE(DRT,'Tot') `DRT`
    ........
    
    FROM (SELECT
        LEFT(Zn,2) 'DRT'
    ........

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    ...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    ...
    was this a question? i thought you had solved your own problem...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry Rudy, it was my mistake and I don't not remove this question/error ...


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
  •