需要一些帮助。 提前致谢!
这是我的第一个表: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:00And 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 5I 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_dateIf 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更多推荐
发布评论