Databases
StackOverflow2013
Postsdbo
POAlternate way for union to improve SQL Query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAlternate way for union to improve SQL Query
    primarykey
    data
    text
    <p>Please help me find another solution/block of code to get the same result but avoiding UNION.</p> <p>Here is my code:</p> <pre><code>SELECT EQPID, "EVENTVAL" AS "EVENTVAL", SUM("20120501") AS "20120501", SUM("20120502") AS "20120502", SUM("20120503") AS "20120503", SUM("201204") AS "201204", SUM("201205") AS "201205" FROM ( SELECT EQPID, "EVENTVAL" AS "EVENTVAL", SUM("20120501") AS "20120501", SUM("20120502") AS "20120502", SUM("20120503") AS "20120503", SUM("201204") AS "201204", SUM("201205") AS "201205" FROM ( SELECT EQPID, CASE WHEN EQPID LIKE 'MS-%' THEN 'TKINQTY' END AS "EVENTVAL", CASE WHEN CUTOFF_DATE = '20120501' THEN SUM(QTY) END AS "20120501", CASE WHEN CUTOFF_DATE = '20120502' THEN SUM(QTY) END AS "20120502", CASE WHEN CUTOFF_DATE = '20120503' THEN SUM(QTY) END AS "20120503", CASE WHEN CUTOFF_DATE BETWEEN '20120401' AND '20120430' THEN SUM(QTY) END AS "201204", CASE WHEN CUTOFF_DATE BETWEEN '20120501' AND '20120530' THEN SUM(QTY) END AS "201205" FROM ( SELECT CUTOFF_DATE, EQPID, SUM(TKINQTY) QTY FROM DAILY_DATA WHERE CUTOFF_DATE BETWEEN '20120401' AND '20120530' AND EQPID LIKE 'MS-%' GROUP BY CUTOFF_DATE, EQPID ORDER BY CUTOFF_DATE, EQPID ) GROUP BY EQPID, CUTOFF_DATE ) GROUP BY EVENTVAL, EQPID UNION SELECT EQPID, "EVENTVAL" AS "EVENTVAL", SUM("20120501") AS "20120501", SUM("20120502") AS "20120502", SUM("20120503") AS "20120503",SUM("201204") AS "201204", SUM("201205") AS "201205" FROM ( SELECT EQPID, CASE WHEN EQPID LIKE 'MS-%' THEN 'TKOUTQTY' END AS "EVENTVAL", CASE WHEN CUTOFF_DATE = '20120501' THEN SUM(QTYO) END AS "20120501", CASE WHEN CUTOFF_DATE = '20120502' THEN SUM(QTYO) END AS "20120502", CASE WHEN CUTOFF_DATE = '20120503' THEN SUM(QTYO) END AS "20120503", CASE WHEN CUTOFF_DATE BETWEEN '20120401' AND '20120430' THEN SUM(QTYO) END AS "201204", CASE WHEN CUTOFF_DATE BETWEEN '20120501' AND '20120530' THEN SUM(QTYO) END AS "201205" FROM ( SELECT CUTOFF_DATE, EQPID, SUM(TKOUTQTY) QTYO FROM DAILY_DATA WHERE CUTOFF_DATE BETWEEN '20120401' AND '20120530' AND EQPID LIKE 'MS-%' GROUP BY CUTOFF_DATE, EQPID ORDER BY CUTOFF_DATE, EQPID ) GROUP BY EQPID, CUTOFF_DATE ) GROUP BY EVENTVAL, EQPID ) GROUP BY ROLLUP(EVENTVAL, EQPID) ORDER BY EQPID, EVENTVAL </code></pre> <p><strong>Result:</strong></p> <pre><code>EQPID | EVENTVAL | 20120501 | 20120502 | 20120503 | 201204 | 201205 ____________________________________________________________________ MS-A1 | TKINQTY | 21528 | 28386 | 18288 | 821295 | 908602 MS-A1 | TKOUTQTY | 21510 | 28359 | 18720 | 820470 | 907320 MS-B1 | TKINQTY | 21530 | 28456 | 18852 | 821560 | 908564 AND SO ON.... </code></pre> <p>Assuming that I would add another value in eventval column, like TKQTY, I would have to use another UNION statement and I don't want to do that. Can you please help me find another solution for this? I have search all over but can't find answer. Please help. thanks a lot.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMudassir Hasan
    1. USQKWS
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI would suggest that you describe what you want to do, providing sample input date and desired results. Your query is quite hard to follow.
      singulars
      1. POAlternate way for union to improve SQL Query
      1. USGordon Linoff
    2. COI have edited by post and included a sample result. Thanks for the advice @GordonLinoff. I have a table name daily_data, with columns: Cutoff_date, EQPID, TKINTQY, TKOUTQTY.. I group the same values in cutoff_date and eqpid then get the sum of tkinqty and tkoutqty. then uses case and union to get the result above.
      singulars
      1. POAlternate way for union to improve SQL Query
      1. USQKWS
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload