Jump to content

Query Union All


safetyweek

Recommended Posts

Hi there, I need your help.I need union the queries for this output:

DSPq	DSP	asx	nos	ins	sca	inl	ese	tot_1XI	  T	   72	 0	 0	   72	 39	498	2.638XM	  L	   2	  0	 0	   2	  2	 2.459  5.646XO	  C	   52	 0	 0	   70	 5	 3.204  7.894XS	  S	   76	 0	 0	   145	44	392	2.456Tot	Tot	  202	0	 0	   289	90	6.553 18.634

Try this Query Union, but I have this output:

DSPq	|	DSP	|	tot	| asxXI	  |	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.

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`    , `asx`FROM(SELECT 	ZN 'DSP'  , REPLACE(FORMAT(COUNT(*),0), ',', '.') 'tot'   , NULL 'asx'	FROM tbl_1	WHERE 1 AND ZN<>'' 	GROUP BY ZN WITH ROLLUP) xUNIONSELECT 	  NULL   ,  NULL   , `tot`   , `asx`FROM  (SELECT 	ZN 'DSP'  , NULL 'tot'  , REPLACE(FORMAT(SUM(CASE WHEN ASX = 1 THEN 1 ELSE 0 END),0), ',', '.') 'asx'FROM tbl_1_sWHERE 1 AND ZN<>'' GROUP BY ZN WITH ROLLUP) x;

Link to comment
Share on other sites

Is the problem that the values of asx are different or that you need the additional columns?
thanks for your reply.the problem is the output, the values of asx are rights.I need this output:
DSPq	|	DSP	|	tot	| asxXI	  |	T	  |	2.638  | 72XM	  |	L	  |	5.646  | 2XO	  |	C	  |	7.894  | 70XS	  |	S	  |	2.456  | 145Tot	 |	Tot	|	18.634 | 289

Not this:

DSPq	|	DSP	|	tot	| asxXI	  |	T	  |	2.638  |XM	  |	L	  |	5.646  |XO	  |	C	  |	7.894  |XS	  |	S	  |	2.456  |Tot	 |	Tot	|	18.634 |									72									2									70									145									289

Link to comment
Share on other sites

If you're using ASP then you can lay out that data however you want. There's nothing in the query telling it to lay out the data like that. In fact, there are no SQL commands at all to tell it how to lay out the data.
I don't not... this is the output/format generate with ASP, what is wrong? :
<div align=center><center><TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 style="border: 1px Solid #6699CC;"><tr><td><TABLE BORDER=1 CELLPADDING=2 CELLSPACING=2 style="font-size=11px;font-family:tahoma;" width="100%" BGCOLOR=WHITE><tr bgcolor=#dcdcdc><td align=center><b>DSP</b></td><td align=center><b>tot</b></td><td align=center><b>asx</b></td><tr bgcolor=#F6F6F6><td align=center>T</td><td align=center>2.638</td><td align=center></td><tr bgcolor=#F6F6F6><td align=center>L</td><td align=center>5.646</td><td align=center></td><tr bgcolor=#F6F6F6><td align=center>C</td><td align=center>7.894</td><td align=center></td><tr bgcolor=#F6F6F6><td align=center>S</td><td align=center>2.456</td><td align=center></td><tr bgcolor=#F6F6F6><td align=center>Tot</td><td align=center>18.634</td><td align=center></td><tr bgcolor=#F6F6F6><td align=center></td><td align=center></td><td align=center>72</td><tr bgcolor=#F6F6F6><td align=center></td><td align=center></td><td align=center>2</td><tr bgcolor=#F6F6F6><td align=center></td><td align=center></td><td align=center>70</td><tr bgcolor=#F6F6F6><td align=center></td><td align=center></td><td align=center>145</td><tr bgcolor=#F6F6F6><td align=center></td><td align=center></td><td align=center>289</td></tr></table></td></tr></table>

Link to comment
Share on other sites

You only have one </tr> tag. Each <tr> needs to be closed before you start the next one.
Ok, but I don't have change in the code:
<html><head><meta http-equiv="Content-Type" content="text/html; charset=windows-1252"><title>Output</title></head><body><div align=center><TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 style="border: 1px Solid #6699CC;"><tr><td><TABLE BORDER=1 CELLPADDING=2 CELLSPACING=2 style="font-size=11px;font-family:tahoma;" width="100%" BGCOLOR=WHITE><tr bgcolor=#dcdcdc><td align=center><b>DSP</b></td><td align=center><b>tot</b></td><td align=center><b>asx</b></td><tr bgcolor=#F6F6F6><td align=center>T</td><td align=center>2.638</td><td align=center></td></tr><tr bgcolor=#F6F6F6><td align=center>L</td><td align=center>5.646</td><td align=center></td></tr><tr bgcolor=#F6F6F6><td align=center>C</td><td align=center>7.894</td><td align=center></td></tr><tr bgcolor=#F6F6F6><td align=center>S</td><td align=center>2.456</td><td align=center></td></tr><tr bgcolor=#F6F6F6><td align=center>Tot</td><td align=center>18.634</td><td align=center></td></tr><tr bgcolor=#F6F6F6><td align=center></td><td align=center></td><td align=center>72</td></tr><tr bgcolor=#F6F6F6><td align=center></td><td align=center></td><td align=center>2</td></tr><tr bgcolor=#F6F6F6><td align=center></td><td align=center></td><td align=center>70</td></tr><tr bgcolor=#F6F6F6><td align=center></td><td align=center></td><td align=center>145</td></tr><tr bgcolor=#F6F6F6><td align=center></td><td align=center></td><td align=center>289</td></tr></tr></table></td></tr></table></div></body></html>

Link to comment
Share on other sites

You have one set of rows that have values for the first 2 columns only, then another set of rows that has a value for the last column only. You only need one set of rows, and it should have values for all three columns.These two rows:<tr bgcolor=#F6F6F6><td align=center>T</td><td align=center>2.638</td><td align=center></td></tr><tr bgcolor=#F6F6F6><td align=center></td><td align=center></td><td align=center>72</td></tr>Should be this:<tr bgcolor=#F6F6F6><td align=center>T</td><td align=center>2.638</td><td align=center>72</td></tr>

Link to comment
Share on other sites

Thks, I understand your suggestion but the problem is the output is generate with this code ASP:

<!-- #include virtual="/include/conn_mysql.asp"--><%	   sql = " SELECT "..." //The query union of my #1 post			  	   Set rs = Server.CreateObject("ADODB.Recordset")	   rs.Open sql, cn	  	   if not rs.eof then				Response.write "<br><div align=center>"				Response.write "<center>"				Response.write "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 style=""border: 1px Solid #6699CC;""><tr><td>"						Response.write "<TABLE BORDER=1 CELLPADDING=2 CELLSPACING=2 style=""font-size=11px;font-family:tahoma;"" width=""100%"" BGCOLOR=WHITE>"						Response.Write "<tr bgcolor=#dcdcdc>"																				Response.Write "<td align=center><b>DSP</b></td>"				Response.Write "<td align=center><b>Tot</b></td>"				Response.Write "<td align=center><b>asx</b></td>"	   Do While Not rs.EOF	   If count Mod 2 = 0 Then bgclr = "#F6F6F6" Else bgclr = "#EFEFEF" End if 	  				Response.Write "<tr bgcolor=" & bgclr & ">"				Response.Write "<td align=center>" & rs("DSP") & "</td>"				Response.Write "<td align=center>" & rs("tot") & "</td>"				Response.Write "<td align=center>" & rs("asx") & "</td>"	   rs.MoveNext()	   Loop				Response.Write "</tr>"																Response.Write "</table>"				Response.write "</td></tr></table>"//FINE VALORI TABELLA		   	   end if	   	   rs.Close()	   set rs = Nothing	   cn.Close()	   set cn = Nothing%>

Link to comment
Share on other sites

I understand. It sounds like you want to use a join instead of a union. A union will just take two sets of rows and display them one after the other. One of your sets has values in some columns, and the other has values in other columns. Instead of doing a union you should join them so that the values from one set are put into the other set instead of just showing them one after the other.

Link to comment
Share on other sites

Thks, I try your suggestion and use Join in the two tables, but I have this output:

DSPq	DSP	asx	nos	ins	sca	inl	ese	supertotXI	  T	   72	 0	 0	   72	 39	498	498XM	  L	   2	  0	 0	   2	  2	 2.459  2.459XO	  C	   52	 0	 0	   70	 5	 3.204  3.204XS	  S	   76	 0	 0	   145	44	392	392Tot	Tot	  202	0	 0	   289	90	6.553  6.553

And not this (correct):

DSPq	DSP	asx	nos	ins	sca	inl	ese	supertotXI	  T	   72	 0	 0	   72	 39	498	2.638XM	  L	   2	  0	 0	   2	  2	 2.459  5.646XO	  C	   52	 0	 0	   70	 5	 3.204  7.894XS	  S	   76	 0	 0	   145	44	392	2.456Tot	Tot	  202	0	 0	   289	90	6.553 18.634

The sql query:

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`     , `as`   , `nos`   , `ins`   , `sca`   , `inl`   , `ese`   , `supertot`  FROM  (SELECT 	ZN 'DSP'  , REPLACE(FORMAT(SUM(CASE WHEN ASX = 1 THEN 1 ELSE 0 END),0), ',', '.') 'asx'  , 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'  , COUNT(CA.xID) 'supertot'FROM tbl_1_s AJOIN tbl_1 CA ON CA.CODE = A.CODEWHERE 1 AND ZN<>'' GROUP BY ZN WITH ROLLUP) x;

Because I don't have CA.CODE = A.CODE always 1=1.Do you understand me?

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...