Join 3,415 readers in helping fund MetaFilter (Hide)

Tags:

Yet another SQL question
December 20, 2010 8:14 AM   Subscribe

How can I change this SQL query?

Our inventory management program allows the export of data via SQL. We already have a script which to display the sales of all items within a certain time period (see below). I would like to modify this script so that I can use it to show within one table how much we sold of an item within every week or every day of a year.

Note: My SQL skills are very limited, so please explain to me this as simple as possible.

The current script:

SELECT ARTIKEL.KURZNAME AS ITEM, SUM(JOURNALPOS.MENGE) AS SOLD
FROM JOURNALPOS
INNER JOIN ARTIKEL
ON ARTIKEL.REC_ID = JOURNALPOS.ARTIKEL_ID
INNER JOIN JOURNAL
ON JOURNAL.REC_ID = JOURNALPOS.JOURNAL_ID
WHERE JOURNAL.RDATUM BETWEEN '2010-12-01' AND '2011-12-15' AND JOURNAL.STADIUM = 2
GROUP BY ARTIKEL.REC_ID
ORDER BY ARTIKEL.KURZNAME


Results in

ITEM | SOLD
Item 1 | 8393
Item 2 | 8997

What I want is this:

ITEM | 2010-12-01 | 2010-12-02 | 2010-12-03 | ...
Item 1 | 87 | 89 | 67 | ...
Item 2 | 997 | 89 | 88 | ...

Thanks a lot!
posted by jfricke to Computers & Internet (6 answers total) 1 user marked this as a favorite
 
This is difficult to put in a SQL query, not to mention inadvisable, because SQL usually relies on there being a fixed number of columns, whereas months may have different numbers of dates. What you want is best put in a crosstab or (in Excel-speak) PivotTable - so add JOURNAL.RDATUM to your select to get a resultset looking like
ITEM   | SOLD | DATE
Item 1 | 87   | 2010-12-01
Item 1 | 89   | 2010-12-02
....
Item 2 | 997  | 2010-12-01
Item 2 | 89   | 2010-12-02
etc.

Then use a report development tool (Reporting Services, Crystal, etc.) to create a crosstab or Excel to create a PivotTable.
posted by Electric Dragon at 8:25 AM on December 20, 2010 [1 favorite]


Electric Dragon has the right idea. This is simply how SQL queries are structured. One "datapoint" per line. Always. Always. Always.

If you're using PHP to output the data, you could manipulate the data to fit your format fairly easily. First sort your query by item, then date.

Pseudocode:
$results=array();
$dates=array();

foreach(sql_fetch_object() as $result){
   $results[] = $result;
   if(!in_array($result->date,$dates)){
      $dates[] = $result->date
   }
}

$dates = sort($dates);

echo "<table><tr>";

foreach ($dates as $date){
   echo "<th>" . $date . "</th>";
}

echo "</tr>";

$prev_item="";

foreach ($results as $result){
   if($result->item != $prev_item){
      echo "</tr><tr><td>" . $result->item . "</td>";
   }
   echo "<td>". $result->numSold . "</td>";
   $prev_item = $result->item;
}

echo "</tr></table>

There's probably a better way to do this, but that should do you.... Also, things will go horribly wrong with that block of pseudocode if you have a date on which you only sold Item 1, and not Item 2, ie:

ITEM | SOLD | DATE
Item 1 | 87 | 2010-12-01
Item 1 | 89 | 2010-12-02
Item 1 | 42 | 2010-12-03
....
Item 2 | 997 | 2010-12-01
Item 2 | 89 | 2010-12-03


Unless you could be absolutely sure to enter a "Item 2, 0 Sold, 2010-12-02" line, you're going to run into problems. Among other things, this is why your original query is not possible.
posted by schmod at 8:52 AM on December 20, 2010 [1 favorite]


Which SQL dialect does this program use? Certain database servers (Microsoft SQl Server) have proprietary extensions for pivoting (T-SQL has the "PIVOT" keyword). If that's not an option, why not select item, date and amount, output this into Excel and just do the pivoting there?
If it absolutely has to be SQL, you could do some evil things with

SELECT item,
CASE WHEN datepart(dd, rdatum) = 1 THEN Menge END as 01,
CASE WHEN datepart(dd, rdatum) = 2 THEN Menge END as 02,
...

then aggregate the result for items.
posted by Nightwind at 9:52 AM on December 20, 2010


Oracle 11g also has the PIVOT keyword to accomplish this task.
posted by SirOmega at 10:24 AM on December 20, 2010


To get results like this:
ITEM | SOLD | DATE
Item 1 | 87 | 2010-12-01
Item 1 | 89 | 2010-12-02
Item 1 | 42 | 2010-12-03
....
Item 2 | 997 | 2010-12-01
Item 2 | 89 | 2010-12-03

You can rewrite the query like this:

SELECT a.kurzname AS item,
 DATE(j.rdatum) AS date,
 SUM(p.menge) AS sold
FROM journal j
 JOIN journalpos p ON ( p.journal_id = j.rec_id )
 JOIN artikel a ON ( a.rec_id = p.artikel_id )
WHERE j.rdatum BETWEEN '2010-12-01' AND '2011-12-5'
 AND j.stadium = 2
GROUP BY a.kurzname,
 DATE(j.rdatum)
ORDER BY a.kurzname ASC,
  DATE(j.rdatum) ASC;

(I reordered some of the tables and adjusted case to make it easier to read. There may be typos or stupid errors.)
posted by xorry at 11:04 AM on December 20, 2010


For weeks, at least in MySQL, you can simply replace each instance of DATE with WEEK in xorry's query.
posted by scottreynen at 6:32 PM on December 20, 2010


« Older Is "New Yorker" a eu...   |  Help me organize a wine tastin... Newer »
This thread is closed to new comments.