php选择查询以按日期排序销售的商品数量(php select query to sum quantity of items sold order by date)

需要一些帮助。提前致谢！

这是我的第一个表：table_bill

id table_id status added_date 73 1 1 9/1/2013 0:00 74 8 1 9/1/2013 0:00 75 17 1 9/1/2013 0:00 76 15 1 9/1/2013 0:00 77 20 1 9/1/2013 0:00 78 10 1 9/1/2013 0:00 79 4 1 9/1/2013 0:00 81 8 1 9/1/2013 0:00 82 16 1 9/1/2013 0:00 83 17 1 9/1/2013 0:00 84 14 1 9/1/2013 0:00 85 10 1 9/1/2013 0:00 86 9 1 9/1/2013 0:00 87 8 1 9/2/2013 0:00 88 11 1 9/2/2013 0:00 89 14 1 9/2/2013 0:00 90 2 1 9/2/2013 0:00 91 12 1 9/2/2013 0:00 92 30 1 9/2/2013 0:00 93 14 1 9/2/2013 0:00 94 5 1 9/2/2013 0:00 95 10 1 9/2/2013 0:00 96 2 1 9/2/2013 0:00 97 10 1 9/2/2013 0:00 98 11 1 9/3/2013 0:00 99 8 1 9/3/2013 0:00 100 11 1 9/3/2013 0:00 101 12 1 9/3/2013 0:00 102 20 1 9/3/2013 0:00 103 4 1 9/3/2013 0:00

这是我的第二个表：table_data

id bill_id item_id quantity 166 73 21 2 167 73 31 1 168 73 115 1 169 73 183 1 170 73 131 8 171 73 170 4 172 73 63 4 173 74 103 1 174 74 187 1 175 74 101 1 177 74 207 1 178 74 136 5 179 74 170 2 180 74 65 2 181 75 25 2 182 75 36 1 183 75 180 1 184 75 65 2 185 75 108 1 187 75 135 2 188 75 141 2 189 75 170 2 190 76 202 1 191 76 118 1 192 76 136 5 193 76 170 3 194 76 63 4 195 77 188 2 196 77 110 1 197 77 63 5

我想得到每天在日期销售的每件商品的总和

这是我对同样的查询....

$sql = "SELECT ocs.item_id, os.added_date, ocs.quantity FROM table_bill os, table_data ocs WHERE os.id = ocs.bill_id";

但是，我一天多次获得销售商品结果

例如：如果项目编号1在一天内出售给5个不同的客户，我对项目编号1得到5个不同的结果，我想要一天中该项目编号的总销售数量。

任何帮助将不胜感激。如果我忘记提及任何事情，请告诉我。谢谢！

Need some help on this. Thanks in advance!

Here is my first table: table_bill

id table_id status added_date 73 1 1 9/1/2013 0:00 74 8 1 9/1/2013 0:00 75 17 1 9/1/2013 0:00 76 15 1 9/1/2013 0:00 77 20 1 9/1/2013 0:00 78 10 1 9/1/2013 0:00 79 4 1 9/1/2013 0:00 81 8 1 9/1/2013 0:00 82 16 1 9/1/2013 0:00 83 17 1 9/1/2013 0:00 84 14 1 9/1/2013 0:00 85 10 1 9/1/2013 0:00 86 9 1 9/1/2013 0:00 87 8 1 9/2/2013 0:00 88 11 1 9/2/2013 0:00 89 14 1 9/2/2013 0:00 90 2 1 9/2/2013 0:00 91 12 1 9/2/2013 0:00 92 30 1 9/2/2013 0:00 93 14 1 9/2/2013 0:00 94 5 1 9/2/2013 0:00 95 10 1 9/2/2013 0:00 96 2 1 9/2/2013 0:00 97 10 1 9/2/2013 0:00 98 11 1 9/3/2013 0:00 99 8 1 9/3/2013 0:00 100 11 1 9/3/2013 0:00 101 12 1 9/3/2013 0:00 102 20 1 9/3/2013 0:00 103 4 1 9/3/2013 0:00

And here is my second table: table_data

id bill_id item_id quantity 166 73 21 2 167 73 31 1 168 73 115 1 169 73 183 1 170 73 131 8 171 73 170 4 172 73 63 4 173 74 103 1 174 74 187 1 175 74 101 1 177 74 207 1 178 74 136 5 179 74 170 2 180 74 65 2 181 75 25 2 182 75 36 1 183 75 180 1 184 75 65 2 185 75 108 1 187 75 135 2 188 75 141 2 189 75 170 2 190 76 202 1 191 76 118 1 192 76 136 5 193 76 170 3 194 76 63 4 195 77 188 2 196 77 110 1 197 77 63 5

I want to get the sum of each item sold in a day datewise

Here is my query for the same....

$sql = "SELECT ocs.item_id, os.added_date, ocs.quantity FROM table_bill os, table_data ocs WHERE os.id = ocs.bill_id";

However, i am getting sold item results multiple time for a day

For ex: if item number 1 is sold to 5 different customers in a day, i am getting 5 different results for item number 1, i want the total number of sale for that item number in a day.

Any help would be greatly appreciated. Let me know if I forgot to mention anything. Thanks!

最满意答案

如果要总计，则必须使用SUM()聚合函数，并使用GROUP BY指定分组。

SELECT ocs.item_id, os.added_date, SUM(ocs.quantity) total FROM table_bill os JOIN table_data ocs ON os.is = ocs.bill_id GROUP BY ocs.item_id, os.added_date

If you want to total things, you have to use the SUM() aggregation function, and specify the grouping with GROUP BY.

SELECT ocs.item_id, os.added_date, SUM(ocs.quantity) total FROM table_bill os JOIN table_data ocs ON os.is = ocs.bill_id GROUP BY ocs.item_id, os.added_date

更多推荐