Wednesday, May 30, 2007

More gadgeteering....

I am a great music fan, and from time to time can't stop myself from singing a song or two, mostly rock classics. Although I know a lot of lyrics, often I can't recall them all.

Now they are all out there on the net, but sometimes difficult to find. So I made a small Google custom search engine, and a gadget to go with it, like this:

Surprisingly what you can do with so little, the power of the web is amazing!

Wednesday, May 16, 2007

Pricerunner Google Gadget

Made another Google gadget the other day, this time doing a search in Pricerunner, to compare prices and products. I believe Pricerunner is a very good service, and widely used in Sweden.

The gadget looks like this:

Today it supports Pricerunner in the UK, Germany, Sweden and Denmark. Hopefully I will be able to add US and France soon.

You could also try it here.

Add Pricerunner to your Google homepage.

Thursday, May 10, 2007

Turning the result around

In many cases you don't want your the result from your SQL statement in just a plain listing, but organized in columns, or whith data grouped on status, period or whatever. This can often be accomplished with a combination of the IF function and SUM or another group function.

For example get your sales per customer grouped by period:
SELECT customer_no AS CUSTNO, cust_name AS CUSTNAME,
sum(if(period = '200701',amount,0) as Jan_Amount,
sum(if(period = '200702',amount,0) as Jan_Amount
FROM otd.salestrans_hist s
WHERE period IN ('200701','200702')
GROUP BY customer_no, cust_name

Or number of open and closed records in each class:
SELECT class, sum(if(status='Open',1,0)) as open,
sum(if(status='Closed',1,0)) as closed
FROM table_name
GROUP BY class

In the examples I have used the MySQL IF function. You could also use CASE, which is SQL standard.