Hello,
I am on Oracle 11g and I am trying to formulate a SQL query to produce results using a PIVOT (not sure if it is actually necessary). The report that I want to create has different "Groups" of information and I was wondering if this could be achieved with a single query. I know I can do this if I run multiple queries, then combine the information, then do some other operations to get what I need, but there are too many steps if I do it this way.
Here is what I want to do... I have many stores and different items sold at each store. For my purposes, I am only concerned with certain stores and certain items. I also have a timestamp field that tells me when the item arrived and when it was sold. I need to calculate the difference between the 2 timestamp fields (sold minus arrived, what is the difference between?)
- Stores - varchar2
- Items - varchar2
- Arrived - Timestamp
- Sold - Timestamp
Below is a sample output of what I want the report to look like. Note that the column headers (2019-08-01, 2019-08-02, and 2019-08-03) would be dynamic (so I could have more dates across), but effectively, this would be the date difference of when the ITEM was sold. For example, DOWNTOWN, PENCIL, 2019-08-01 ==> 5. This is the calculated total number of days for all PENCILS sold that date.
If I had 5 PENCILS and they all had an Arrival Date of 2019-07-31 and a Sold Date of 2019-08-01. This would equal 5 days.
If I had 3 PENCILS...
- PENCIL 1 Arrival Date 2019-07-31 and Sold Date 2019-08-01 ==> 1 Day
- PENCIL 2 Arrival Date 2019-07-30 and Sold Date 2019-08-01 ==> 2 Days
- PENCIL 3 Arrival Date 2019-07-30 and Sold Date 2019-08-01 ==> 2 Days
- Total Days ==> 5 days
| STORE | ITEMS
| 2019-08-01
| 2019-08-02 | 2019-08-03 |
|---|
| DOWNTOWN | PEN | 1 | 2 | 3 |
| DOWNTOWN | PENCIL | 5 | 6 | 7 |
| DOWNTOWN | ERASER | 2 | 1 | 3 |
| | 2.67 (AVERAGE DATE DIFFERENCE) | 3 (AVERAGE DATE DIFFERENCE) | 4.33 AVERAGE DATE DIFFERENCE |
| 4.SUBURB | PEN | 9 | 8 | 7 |
| SUBURB | PENCIL | 3 | 2 | 1 |
| SUBURB | ERASER | 4 | 5 | 6 |
| | 5.33 AVERAGE DATE DIFFERENCE | 5 AVERAGE DATE DIFFERENCE | 4.67 AVERAGE DATE DIFFERENCE |
Here is my attempt SQL query...
select * from (
select sn.storenamecolumn, i.itemcolumn, trunc(d.solddatecolumn)
from itemtable i
join storetable s on i.id = s.id
join storenametable sn on s.storeid = sn.storename
join itemdatetable d on i.itemid = d.id
where i.itemcolumn in ('PEN', 'PENCIL', 'ERASER')
and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd')
)
pivot
(
count(*)
for trunc(solddatecolumn) in ('2019-08-01', '2019-08-02', '2019-08-03') <============ error missing IN keyword
)
where storenamecolumn in ('DOWNTOWN', 'SUBURB')
order by storenamecolumn;
I keep getting the error above and I believe it has to do with the trunc() method. If I remove the trunc() method from both the "for trunc()" and the SELECT Statement, then it will run, but the results are not right because I am only looking for a date and not the entire "timestamp". Any suggestions on how I can achieve my desired results? Thanks.
Also, is it possible to calculate the total average for each day and put it at the bottom of each Date column? I edited the table above.