SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Queries Union

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

    Queries Union

    Hi there, I need your help and new lesson in MySQL.

    I need union this queries for this output:
    Code:
    DSPq	DSP	ass	nos	ins	sca	inl	ese	tot_1
    XI	T	72	0	0	72	39	498	2.638
    XM	L	2	0	0	2	2	2.459	5.646
    XO	C	52	0	0	70	5	3.204	7.894
    XS	S	76	0	0	145	44	392	2.456
    Tot	Tot	202	0	0	289	90	6.553	18.634
    I try with the JOIN syntax but I have this response: [Err] 2013 - Lost connection to MySQL server during query.

    Can you help me? It's possible this union?
    Thanks in advance.

    Code:
    SELECT 
          COALESCE(DSP,'Tot') `DSPq` 
       ,  CASE DSP 
          WHEN 'XS' THEN 'S' 
          WHEN 'XO' THEN 'C' 
          WHEN 'XM' THEN 'L' 
          WHEN 'XI' THEN 'T' 
          ELSE 'Tot' END `DSP`  
       , `tot_1`
      FROM 
     (SELECT 
        ZN 'DSP'
      , COUNT(*) 'tot_1' 
    FROM tbl_1
    WHERE 1 AND Zn<>'' 
    GROUP BY ZN WITH ROLLUP) x;
    
    ###########
    
    SELECT 
          COALESCE(DSP,'Tot') `DSPq` 
       ,  CASE DSP 
          WHEN 'XS' THEN 'S' 
          WHEN 'XO' THEN 'C' 
          WHEN 'XM' THEN 'L' 
          WHEN 'XI' THEN 'T' 
          ELSE 'Tot' END `DSP`  
       , `ass`
       , `nos`
       , `ins`
       , `sca`
       , `inl`
       , `ese`
      FROM 
     (SELECT 
        ZN 'DSP'
      , REPLACE(FORMAT(SUM(CASE WHEN ASS = 1 THEN 1 ELSE 0 END),0), ',', '.') 'ass'
      , REPLACE(FORMAT(SUM(CASE WHEN DATEDIFF(Date_off, CURRENT_DATE()) > 5 THEN 1 ELSE 0 END),0), ',', '.') 'nos' 
      , REPLACE(FORMAT(SUM(CASE WHEN DATEDIFF(Date_off, CURRENT_DATE()) BETWEEN 1 AND 5 THEN 1 ELSE 0 END),0), ',', '.') 'ins' 
      , REPLACE(FORMAT(SUM(CASE WHEN DATEDIFF(Date_off, CURRENT_DATE()) < 1 THEN 1 ELSE 0 END),0), ',', '.') 'sca'
      , REPLACE(FORMAT(SUM(CASE WHEN STA = 1 THEN 1 ELSE 0 END),0), ',', '.') 'inl'
      , REPLACE(FORMAT(SUM(CASE WHEN ACC = 1 THEN 1 ELSE 0 END),0), ',', '.') 'ese'
    FROM tbl_1_s
    WHERE 1 AND ZN<>'' 
    GROUP BY ZN WITH ROLLUP) x;

  2. #2
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Try this Query Union, but I have this output:
    Code:
    DSPq	DSP	tot	ass
    XI	T	2.638
    XM	L	5.646
    XO	C	7.894
    XS	S	2.456
    Tot	Tot	18.634
    			72
    			2
    			70
    			145
    			289
    Can you help me?
    Thanks in advance.

    Code:
    SELECT 
          COALESCE(DSP,'Tot') `DSPq` 
       ,  CASE DSP 
          WHEN 'XS' THEN 'S' 
          WHEN 'XO' THEN 'C' 
          WHEN 'XM' THEN 'L' 
          WHEN 'XI' THEN 'T' 
          ELSE 'Tot' END `DSP`
       , `tot` 
       , `ass`
    FROM
    (
    SELECT 
        ZN 'DSP'
      , REPLACE(FORMAT(COUNT(*),0), ',', '.') 'tot' 
      , NULL 'ass'
        FROM tbl_1
        WHERE 1 AND ZN<>'' 
        GROUP BY ZN WITH ROLLUP) x
    
    UNION
    
    SELECT 
          NULL
       ,  NULL
       , `tot`
       , `ass`
    FROM 
     (SELECT 
        ZN 'DSP'
      , NULL 'tot'
      , REPLACE(FORMAT(SUM(CASE WHEN ASS = 1 THEN 1 ELSE 0 END),0), ',', '.') 'ass'
    FROM tbl_1_s
    WHERE 1 AND ZN<>'' 
    GROUP BY ZN WITH ROLLUP) x;


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
  •