StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to derive headers from a list of data based off a where clause and a subquery
text
Body
copied!<p>I am trying to generate a list derived from two tables. The end result should be:</p> <pre><code>Date A OHS OMSN OMSS date1 1 1 2 3 date2 4 5 6 7 date... .. .. .. .. </code></pre> <p>The date ranges are generated off of a calendare table I generated and are being right joined so they are included even if the count for the other buildings are 0. Under each building there is a count that should simply count the workorders for each day. Here is the query I have so far:</p> <pre><code>Select calendar.datefield As 'Date', Count(FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d')) As 'Count' From workorders Right Join calendar On (FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') = calendar.datefield) Where calendar.datefield Between '2011-08-30' And date_format(now(), '%Y-%m-%d') Group By calendar.datefield </code></pre> <p>This query gives me the total workorders for all locations for each day on a specific range. Including this is fine but I want to have 3 more columns that are filtered by each specific loction. If I add the building name like this:</p> <pre><code>Select calendar.datefield As 'Date', workorders.location, Count(FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d')) As 'Count' From workorders Right Join calendar On (FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') = calendar.datefield) Where calendar.datefield Between '2011-08-30' And date_format(now(), '%Y-%m-%d') Group By calendar.datefield, workorders.location </code></pre> <p>Then the table gives me the data i need but not the columns I am looking for. The data will be somethine lie this:</p> <pre><code>2011-09-08 OHS 27 2011-09-09 OHS 24 2011-09-10 NULL 0 2011-09-11 NULL 0 2011-09-12 OHS 23 2011-09-13 OHS 18 2011-09-13 OMS-N 1 2011-09-14 OHS 20 </code></pre> <p>I want to pull from this query by selecting the date, then the numbers where it is OHS, then the numbers where it is OMSN, then the numbers where it is OMSS etc...</p> <p>There has to be an easier way to do this and I have literally worked tall day trying to piece this query together.</p> <p>I hope someone can help. I learned mysql on the fly over the past few months and any help is much appreciated.</p> <p>_<em>_</em> FURTHER REVIEW:</p> <pre><code>SELECT calendar.datefield, tOHS.Count AS 'OHS' FROM calendar LEFT JOIN ( SELTCT workorders.school, Count( workorders.dateSubmitted ) AS 'Count', FROM_UNIXTIME( workorders.dateSubmitted, '%Y-%m-%d' ) AS test FROM workorders RIGHT JOIN calendar ON ( FROM_UNIXTIME( workorders.dateSubmitted, '%Y-%m-%d' ) = calendar.datefield ) WHERE calendar.datefield BETWEEN '2011-08-30' AND date_format( now( ) , '%Y-%m-%d' ) AND School = 'OHS' GROUP BY calendar.datefield, workorders.school )tOHS ON calendar.datefield = tOHS.test WHERE calendar.datefield BETWEEN '2011-08-30' AND date_format( now( ) , '%Y-%m-%d' ) </code></pre> <p>This returns the dates and the daily count for the first location OHS. WHEN I try this:</p> <pre><code>select calendar.datefield, tOHS.Count as 'OHS' from calendar LEFT JOIN ( Select workorders.school, Count(workorders.dateSubmitted) As 'Count', FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') as test From workorders Right Join calendar On (FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') = calendar.datefield) Where calendar.datefield Between '2011-08-30' And date_format(now(), '%Y-%m-%d') AND School = 'OHS' Group By calendar.datefield,workorders.school ) tOHS on calendar.datefield = tOHS.test LEFT JOIN ( Select workorders.school, Count(workorders.dateSubmitted) As 'Count', FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') as test From workorders Right Join calendar On (FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') = calendar.datefield) Where calendar.datefield Between '2011-08-30' And date_format(now(), '%Y-%m-%d') AND School = 'OMS-S' Group By calendar.datefield,workorders.school ) tOMSS on calendar.datefield = tOHS.test WHERE calendar.datefield Between '2011-08-30' And date_format(now(), '%Y-%m-%d') </code></pre> <p>It breaks the query all together. I see the same date and the same value repeated for all columns. Am I using the wrong join?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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