title: POS Application - Simplify SQL Mutli-Queries(MySQL)
body: <p>Hei guy I'm working on a POS app with MySQL. Here is my situation:</p>
  
  <p>Table "purchased_item"</p>
  
  <pre><code>|    id    |    name    |    check_id    |    real_price    |
  |    1     |   iPhone5  |      0001      |       399        |
  |    2     |   iPhone4  |      0001      |       199        |
  |    3     |   iPhone5s |      0002      |       599        |
  |    4     |   iPhone5c |      0003      |       399        |
  |    5     |   iMac 21" |      0003      |       999        |
  |    6     | iPod Touch |      0003      |        99        |
  |    7     |   iPhone5  |      0004      |       399        |
  |    8     |   iPhone3G |      0004      |        99        |
  |    9     |   iPhone6  |      0005      |       899        |
  |    10    |  iPhone3Gs |      0005      |       101        |
  </code></pre>
  
  <p>And I want to know how many checks's total are larger than or qual(>=) 1000, so what I'm doing now is to do several times of query. In this example, I do 5 times and sum it manually by the host program.  </p>
  
  <p>Later the data grow, the queries become slow because there're tons of checks everyday. So I change to record it to another table.</p>
  
  <p>Table "checks"</p>
  
  <pre><code>|    id    |    total    |    sales    |
  |   0001   |     598     |      A      |
  |   0002   |     599     |      A      |
  |   0003   |     1497    |      B      |
  |   0004   |     498     |      B      |
  |   0005   |     1000    |      A      |
  </code></pre>
  
  <p>But another problem occur in the later time: When I need to adjust the real_price in "purchased_item" table, I also need to maintain the "total" column in "checks" table. It sounds doesn't a big matter but I'd like to find a better way to solve it.</p>
  
  <p>Solved:</p>
  
  <pre><code>SELECT * FROM purchased_item
  GROUP BY check_id
  HAVING sum(real_price) &gt;= 1000
  </code></pre>
  
  <p>And the result will be:</p>
  
  <pre><code>|    id    |    name    |    check_id    |    real_price    |
  |    4     |   iPhone5c |      0003      |       399        |
  |    9     |   iPhone6  |      0005      |       899        |
  </code></pre>
  
  <p>Further question: If I want to count the total price for checks, how can I do it?
  I found it:</p>
  
  <pre><code>SELECT check_id,sum(real_price) FROM purchased_item
  GROUP BY check_id
  HAVING sum(real_price) &gt;= 1000
  </code></pre>