safetyweek Posted September 1, 2011 Share Posted September 1, 2011 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 More sharing options...
justsomeguy Posted September 1, 2011 Share Posted September 1, 2011 Is the problem that the values of asx are different or that you need the additional columns? Link to comment Share on other sites More sharing options...
safetyweek Posted September 1, 2011 Author Share Posted September 1, 2011 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 More sharing options...
justsomeguy Posted September 1, 2011 Share Posted September 1, 2011 Are you saying it's just a formatting issue? The SQL code doesn't have anything to do with how the data gets displayed. Link to comment Share on other sites More sharing options...
safetyweek Posted September 1, 2011 Author Share Posted September 1, 2011 Are you saying it's just a formatting issue? The SQL code doesn't have anything to do with how the data gets displayed.Yes, the problem is formatting issue, but the output is the response of MySQL and the response with server side language (ASP). Link to comment Share on other sites More sharing options...
justsomeguy Posted September 1, 2011 Share Posted September 1, 2011 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. Link to comment Share on other sites More sharing options...
safetyweek Posted September 1, 2011 Author Share Posted September 1, 2011 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 More sharing options...
justsomeguy Posted September 1, 2011 Share Posted September 1, 2011 You only have one </tr> tag. Each <tr> needs to be closed before you start the next one. Link to comment Share on other sites More sharing options...
safetyweek Posted September 1, 2011 Author Share Posted September 1, 2011 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 More sharing options...
justsomeguy Posted September 1, 2011 Share Posted September 1, 2011 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 More sharing options...
safetyweek Posted September 1, 2011 Author Share Posted September 1, 2011 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 More sharing options...
justsomeguy Posted September 1, 2011 Share Posted September 1, 2011 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 More sharing options...
safetyweek Posted September 2, 2011 Author Share Posted September 2, 2011 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.