Entrepreneur Geek

I am stuck onto a problem and would appreciate some help on this! Here's the problem:

I am using aggregate functions (eg. SUM) of MySQL in a query where two tables are joined. I also need to filter the results based on some WHERE clause. Now the problem is, SUM sums up the values multiple times since one row from one table repeats due to the join.

Let me explain with an example. Suppose the two tables I am working with are transactions (T) and transaction_products (TP). T stores the details of the transaction (date, total price, discount, customerId etc) and TP stores line item details (productId, item price, item discount etc). And I want to get the total sales for an order where a particular product was sold.

Here's what I do:

Now that does not work correctly. And even when I simply want to get the total sales for all orders, the query becomes

And this is wrong! It joins the two tables, and repeats the totalPrice in the result, and sums up those values. Resulting in a much larger sales than the actual

How to solve this?

You can say that we can avoid the join where we want to SUM on a field from T, but that would not help in a condition like this:

If I sum up all the result rows of the above query, the result is about 3 times higher than this:

And the problem is that I need the category Id (or some other field from TP table)!

Any solutions to this?

I thought of derived tables (as mentioned here) but that does not solve the problem.

I thought of splitting the query in two parts. Storing the results of SUM in a temp table and then doing the WHERE clause - but that would fail outright since the results do not include the WHERE condition.

What else can I do? What do you think?

I was talking to Dave this morning, and he said a very beautiful thing:

The two edge sword of loving what you do, is you don't mind doing a lot of it.

Dave is our client and partner in RIA projects and I was discussing with him about recreation, growing business and marketing. Got many insights, especially on marketing a product! I think we are going to consult with him for marketing our next product

How many meetings do you attend every week? Do your meetings tend to take forever? How many informal meetings do you have that go on longer than expected?

I have a limited number of meetings to attend, about 6 hours every week. But sure, we could make them tighter. I saw a post about creating time scarcity in meetings by introducing meeting tokens on 43Folders. And I think it's an interesting concept.

How it works is that at the start of the week, all people (who need to attend / call a meeting) are given tokens. To call a meeting you need sufficient tokens. E.g. 4 tokens of 10 minutes if you want to have a daily standup meeting of 10 minutes with 4 people. You can't go beyond this time, and the tokens lapse as used. The Red Merlin token is a trump token, and can be used to end any meeting without question - of course, Red Merlin is given to only one person!

What do you think about this?

Tokens created by Mike Monteiro.

Want to find out who are the Web 2.0 players? What's the new venture in photo sharing apps? Head on to Go 2 Web 2.0 - a simple, single source for finding the latest and the best Web 2.0 websites.

I like their search and display interface as well! Very clean and Web 2.0ish!

Thanks to Dave for the catch!

Lifehacker has a very interesting article on top 10 ways to sleep smarter and better. In these days when I always feel I can use some more sleep, this article brings some good techniques. My problem is not that I don't get sleep, but I feel I need more 6 hours should be enough for the body, but then I don't feel it's enough!

How much do you sleep? And how do you keep at your best with 6 hours of sleep?