Leo Jones
Reputation: 165
Postgresql union to get sales for all days and hours and a summary row
We have 2 tables and a query to get sales data for every hour and every day, and a summary row for each day.
Tables:
- sales_table - sales data
- date_hour - lists all dates and their respective hours (24 hours each day)
Query:
with b as (
SELECT
a.purchase_date,
CAST(DATE_PART("HOUR", CAST(a.purchase_date AS DATETIME)) as INTEGER) AS
Hour,
COUNT(a.quantity) AS QtyCount,
SUM(a.quantity) AS QtyTotal,
SUM((a.item_price) AS Price
FROM sales_table a
GROUP BY Hour, a.purchase_date
),
a as (
SELECT
DATE_FORMAT(DateT, "yyyy-MM-dd") AS datet,
CAST(hourt AS INTEGER) AS hourt,
QtyCount,
QtyTotal,
Price
FROM date_hour
),
c as (
SELECT
a.purchase_date,
CAST(DATE_PART("HOUR", CAST(a.purchase_date AS DATETIME)) as INTEGER)
AS Hour,
COUNT(a.quantity) AS QtyCount,
SUM(a.quantity) AS QtyTotal,
SUM((a.item_price) AS Price
FROM sales_table a
GROUP BY a.purchase_date
) --summary row subquery
Select --summary row
c.purchase_date,
"Day Total" AS Hour,
if(c.QtyCount is null, 0, c.QtyCount) QtyCount,
if(c.QtyTotal is null, 0, c.QtyTotal) QtyTotal,
if(c.Price is null, 0, c.Price) AS Price
FROM a LEFT JOIN c
ON a.datet = c.purchase_date
UNION
Select
a.datet AS Date,
a.hourt AS Hour,
if(c.QtyCount is null, 0, c.QtyCount) QtyCount,
if(c.QtyTotal is null, 0, c.QtyTotal) QtyTotal,
if(c.Price is null, 0, c.Price) AS Price
FROM a LEFT JOIN b
ON a.datet = b.purchase_date AND a.hourt = b.Hour
ORDER BY Date, c.hour
Sample result:
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Date</th><th>Hour</th><th>QtyCount</th><th>QtyTotal</th><th>Price</th></tr></thead><tbody>
<tr><td>2018-11-02</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>1</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>2</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>3</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>4</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>5</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>6</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>7</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>8</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>9</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>10</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>11</td><td>1</td><td>1</td><td>6.99</td></tr>
<tr><td>2018-11-02</td><td>12</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>13</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>14</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>15</td><td>1</td><td>1</td><td>69.95</td></tr>
<tr><td>2018-11-02</td><td>16</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>17</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>18</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>19</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>20</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>21</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>22</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td>23</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-02</td><td> </td><td>2</td><td>2</td><td>76.94</td></tr>
<tr><td>2018-11-03</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>1</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>2</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>3</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>4</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>5</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>6</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>7</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>8</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>9</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>10</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>11</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>12</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>13</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>14</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>15</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>16</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>17</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>18</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>19</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>20</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>21</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>22</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-03</td><td>23</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>1</td><td>1</td><td>3</td><td>209.85</td></tr>
<tr><td>2018-11-04</td><td>2</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>3</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>4</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>5</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>6</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>7</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>8</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>9</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>10</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>11</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>12</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>13</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>14</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>15</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>16</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>17</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>18</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>19</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>20</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>21</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>22</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td>23</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>2018-11-04</td><td> </td><td>1</td><td>3</td><td>209.85</td></tr>
</tbody></table>The query works but the summary row is not listed at the top. Is it possible to list the summary row at the top of each day (before hour 0 of that day) and include a summary row when there were no sales (11/3 in the above example) as zeros across? Thanks in advance for your help.
Upvotes: 0
Views: 73
Answers (1)
Paul Maxwell
Reputation: 35603
Without examining too deeply I think all you need is a a column to assist with ordering
with (....
...
)
Select --summary row
0 as ordr,
c.purchase_date,
"Day Total" AS Hour,
if(c.QtyCount is null, 0, c.QtyCount) QtyCount,
if(c.QtyTotal is null, 0, c.QtyTotal) QtyTotal,
if(c.Price is null, 0, c.Price) AS Price
FROM a LEFT JOIN c
ON a.datet = c.purchase_date
UNION
Select
1 as ordr,
a.datet AS Date,
a.hourt AS Hour,
if(c.QtyCount is null, 0, c.QtyCount) QtyCount,
if(c.QtyTotal is null, 0, c.QtyTotal) QtyTotal,
if(c.Price is null, 0, c.Price) AS Price
FROM a LEFT JOIN b
ON a.datet = b.purchase_date AND a.hourt = b.Hour
ORDER BY Date, ordr, hour
I suggest you might want to investigate ROLLUP which may simplify your query.somewhat.
To get a summary for every date, you need to use the cte "a" as the base table, an left join the sales_table, see "c" below This is untested and only a proposal, you will need to do any debugging.
with a as (
SELECT
DATE_FORMAT(DateT, "yyyy-MM-dd") AS datet,
CAST(hourt AS INTEGER) AS hourt,
QtyCount,
QtyTotal,
Price
FROM date_hour
)
, b as (
SELECT
a.purchase_date,
CAST(DATE_PART("HOUR", CAST(a.purchase_date AS DATETIME)) as INTEGER) AS
Hour,
COUNT(a.quantity) AS QtyCount,
SUM(a.quantity) AS QtyTotal,
SUM((a.item_price) AS Price
FROM sales_table a
GROUP BY Hour, a.purchase_date
)
, c as (
SELECT
a.datet as purchase_date,
CAST(DATE_PART("HOUR", CAST(st.purchase_date AS DATETIME)) as INTEGER) AS Hour,
COUNT(st.quantity) AS QtyCount,
SUM(st.quantity) AS QtyTotal,
SUM((st.item_price) AS Price
FROM a
left join sales_table st ON a.datet = c.purchase_date
GROUP BY a.datet
) --summary row subquery
btw "a" "b" "c" etc. are very frustrating aliases. I think things like "st" (Sales Table) are more useful.
Upvotes: 1
Related Questions
- Complex grouping of sales in PostgreSQL
- Finding the sum of column data of each row in each day hour in postgresql
- SQL query to find the total number of books sold for each day by merging 2 tables?
- Calculating sales on daily basis comparing the previous sales
- Postgresql - Partial sum per day and overall in one query
- union all and sum
- Count of sales partitioned by DOW (with date and time as input) - postgresql
- postgresql - Sum of columns and group by hour of datetime
- Aggregating postgres union results
- Counting the number of sales for each date in a date range, postgresql