I have a PSQL query where the AND parameter in the left join in on another table check table sample below.
Invoices
id | account | invoice_date | reference | total_amount | status
-----+---------+-------------------------+-----------+--------------+--------
164 | 100 | 2016-08-03 03:05:08.996 | 161 | 2000.00 |
165 | 100 | 2016-08-03 21:42:07.865 | 164 | 0 |
167 | 100 | 2016-08-03 22:56:41.731 | 166 | 100.00 |
168 | 100 | 1970-01-01 00:33:20 | 161 | 200 |
169 | 100 | 2016-08-08 00:00:00 | 161 | 200 |
Invoice_items
id | invoice | invoice_item_type | product | quantity | unit_price | reference | amount
-----+---------+-------------------+---------+----------+------------+-----------+---------
143 | 164 | 1 | 6 | | | 161 | 2000.00
144 | 165 | 1 | 11 | | | 164 | 0
145 | 167 | 1 | 8 | | | 166 | 100.00
There is also another table PRODUCTS but the only relevant field there is id
this is my query
select products.id, sum(invoice_items.amount) as total_revenue
from products
left join invoice_items on invoice_items.product = products.id
left join invoices on invoice_items.invoice = invoices.id
and invoices.invoice_date= current_date
group by products.id;
What i need the query to do is to list all Product ids and on the total_revenue column, put the sum of sales for the product (add 'amount' in invoice_items table where 'product' field is similar) for the current day (found on INVOICES table). But when i run this query it lists all total_amounts for the products. What am i missing?
Sample output. 8 , 6 and 11 must be empty
id | total_revenue
-----+---------------
125 |
154 |
119 |
129 |
8 | 100.00
112 |
5 |
132 |
104 |
113 |
143 |
152 |
121 |
127 |
165 |
139 |
146 |
15 |
2 |
147 |
149 |
166 |
169 |
13 |
106 |
122 |
9 |
11 | 0
110 |
120 |
130 |
155 |
134 |
136 |
101 |
168 |
131 |
157 |
161 |
103 |
150 |
159 |
107 |
108 |
145 |
4 |
12 |
158 |
167 |
138 |
162 |
100 |
156 |
163 |
124 |
123 |
109 |
153 |
102 |
105 |
151 |
116 |
133 |
140 |
160 |
148 |
126 |
141 |
7 |
118 |
10 |
164 |
128 |
14 |
144 |
135 |
1 |
6 | 2000.00
3 |
137 |
117 |
142 |
111 |
The date constraint is only filtering out records in the invoices table, whereas you need it to also filter out the records in the invoice_items table - but it's not doing this because both are left joins. A derived table will resolve this easily, and give the result that you want. I also added some table aliases for conciseness and readability.
Like this:
SELECT
p.id, SUM(inv.amount) AS total_revenue
FROM
products p LEFT JOIN
(SELECT
ii.product, i.invoice_date, ii.amount
FROM
invoice_items ii JOIN
invoices i ON
ii.invoice = i.id) inv ON
inv.product = p.id AND
inv.invoice_date= current_date
GROUP BY p.id;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments