Shantanu's Blog

Database Consultant

June 30, 2005


MySQL Case Study - 36

please take a look a this query:

select * from predmeti p, rjesenja r WHERE p.predmetID = r.predmeti_predmetID

As you can see, here I want to display content from table predmeti and rjesenja. But only if there is a foreign key in rjesenja.

This works fine, it shows the desired results, but..

the vise versa version:

select * from predmeti p, rjesenja r WHERE p.predmetID != r.predmeti_predmetID

does not work, here I want to display all from table predmeti, but only if there is
no foreign key in table rjesenja.

What is wrong?


MySQL Case Study - 35

I imported a bunch of users using their email address as the username. I need to update every user who has an email address in the username field, to convert the username to just what is before the @ symbol.

Example: becomes user

and becomes

Can anybody help with this?

June 29, 2005



The new (version 4.1) ON DUPLICATE KEY UPDATE ... clause for INSERT statements allows special handling when an inserted row would cause a duplicate value in a UNIQUE index (or a PRIMARY KEY). For example, you could record votes in a poll system using a statement like the following:

mysql> INSERT INTO poll (favorite, votes) VALUES ('Hamburger', 1)
-> ON DUPLICATE KEY UPDATE votes = votes 1;

You can use the new VALUES() function to refer to column values from the INSERT part of the query.

mysql> INSERT INTO poll (favorite, votes, last_voter)
VALUES ('Hamburger', 1, 'Wimpy')
-> ON DUPLICATE KEY UPDATE votes = votes 1,
last_voter = VALUES(last_voter);"


ROLLUP provides summary rows for each GROUP BY level.

An ordinary SELECT ... GROUP BY on this table gives this result.

mysql> SELECT col1, col2, SUM(col3) AS sumcol3
-> FROM T_rollup GROUP BY col1,col2;
| col1 | col2 | sumcol3 |
| 1 | a | 1.10 |
| 1 | b | 2.35 |
| 2 | a | 7.77 |
3 rows in set (0.03 sec)

In contrast, the same query, using WITH ROLLUP, gives this result.

mysql> SELECT col1, col2, SUM(col3) AS sumcol3
-> FROM T_rollup GROUP BY col1,col2 WITH ROLLUP;
| col1 | col2 | sumcol3 |
| 1 | a | 1.10 |
| 1 | b | 2.35 |
| 1 | NULL | 3.45 |
| 2 | a | 7.77 |
| 2 | NULL | 7.77 |
| NULL | NULL | 11.22 |
6 rows in set (0.04)

As the result shows, in addition to the groups returned by the regular GROUP BY query, GROUP BY ... WITH ROLLUP also returns a summary row for each group.

Thus, for the group where col1 equals one (1), col2 gets a NULL to indicate a summary row.

A CREATE TABLE ... LIKE statement is a quick way of cloning the structure of an existing table, including any indexes (but not foreign keys). This can be particularly useful in creating new tables to be added to an existing MERGE table.

mysql> CREATE TABLE log_20041124 LIKE log_20041123;

June 28, 2005


Google launches 'Personalized Search'

Personalized Search' (beta) learns from your history of searches and search results you've clicked on, and brings certain results closer to the top when it's clear they're most relevant to you. As you build up your search history, your personalized search results will continue to improve over time.
This is exactly what I had dreamed about in March 04 when Personalized Search was introduced.

June 27, 2005


MySQL Case Study - 34

I'm looking to list the number of Users who have an entry matching "Page View" in the table UserActions. The query I'm using is:

SELECT COUNT(*) AS Download_Ruequests
FROM (SELECT * FROM UserActions,
(SELECT * FROM UserActions WHERE Action LIKE "View%" GROUP BY UserID) AS Users
WHERE UserActions.Action = "Request Download"
GROUP BY UserActions.UserID) AS Downloads

However, it involves 2 subqueries and I can't help but feel there must be a simpler way...

I want to return users who have viewed a page AND requested a download, but each of these actions is in a different row in the table. So I'm effectively saying, show me the User IDs which have this action and another action in a different row.

Any pointers greatly appreciated.


MySQL Case Study - 33

I am trying to count the number of rows returned in a result set, and can't figure out how. For example, I have this SQL:

SELECT count(*)
FROM `4images_sessions_hist` sh
WHERE sh.session_user_id <> -1
group by FROM_UNIXTIME(sh.session_lastaction, '%Y-%m-%d %H'), sh.session_user_id, sh.session_ip

It currently returns:

But I would like it to return the number of rows, i.e. 5

June 26, 2005


Optimizing MySQL: Queries and Indexes

You can provide similar information for the Optimizer by running

ANALYZE TABLE tablename;

This stores the key distribution for the table (running ANALYZE is equivalent to running myisamchk -a or myismachk --analyze).

Many deletes and updates leave gaps in the table (especially when you're using varchar, or in particular text/blob fields). This means there are more unnecessary disk I/O's, as the head needs to skip over these gaps when reading. Running

OPTIMIZE tablename

solves this problem. Both of these statements should be run fairly frequently in any well looked after system.


MySQL Case Study - 32

I have routinely used statements like:
select, from team as t, player as p where; for example
/*return the team name instead of id reference stored in player table*/

I recently had a case where I have players accociated with up to three teams...

select name, team1, team2, team3 from player limit 1;

looks something like: || John Doe | 1 | 2 | 3 ||

I want to return the team names for all three teams, but because the team id's for team1, team 2 and team three will never match i cannot use this

from player as p, team as t
where and and; Odvoiusly won't work

What is the solution for this in mysql 3 < latest 3.xx?

June 24, 2005


MySQL Case Study - 31

I'm looking for a query to return values which occur in one table, but not in the other. Say we have two tables (table_1, table_2) each with a column titled "appointments". I want a list of results for appointments which are in table_1, but not in table_2. I have tried the following, but all I get is a huge list of duplicate appointments which occur in both tables:

SELECT table_1.appointments
FROM table_1, table_2
WHERE table_1.appointments<>table_2.appointments;

I've also tried 'NOT IN' for this but the result was just the same.


Compound Annual growth Rate (CAGR)

You can use the IRR function to calculate the internal rate of return for a series of values. But the XIRR function is used to determine the internal rate of return when there are a number of irregular payments associated with an investment. The XIRR function is provided as part of the Analysis ToolPak add-in. If you put money into your portfolio, and take some out, then in and out etc. etc. then we're NOT talking about a buy-and-hold return. That makes the calculation of Annualized return more complicated, but there's an MS Excel command for this:
The XIRR command. It goes like this:


where the range A1:A50 contains the cash flows
and the range B1:B50 contains the dates
and 0.10 is an initial guess (like 0.10 meaning 10%)

The compound annual growth rate (CAGR) is computed by the formula:
[(Pv / P0) (1/n)]-1
where Pv = Present value
P0 = Beginning value
n = Number of periods
The result is multiplied by 100 to obtain a percentage.

CAGR, is the year over year growth rate applied to an investment
or other part of a activity over a multiple-year period.
The formula for calculating CAGR using a spreadsheet is
=(Current Value/Base Value)^(1/# of years) - 1
To change the number into a percentage, multiplying by 100.

current value _____ 100
base value _______ 80
number of years ___ 10
Excel formula_____ =((B1/B2)^(1/B3)-1)
Result: __________ 0.023
To change to a percent
Result: 2.26

June 22, 2005


Sample tables to learn SQL

CREATE TABLE president (
last_name VARCHAR(15) NOT NULL,
first_name VARCHAR(15) NOT NULL,
suffix VARCHAR(5) NULL, /* name suffix (Jr., II, etc.) */
death DATE NULL );

The data is available in the same directory. You can run these SQL commands to create a sample table and learn how to query data.

June 19, 2005


NewsForge | Interview: Axmark and Behlendorf on OSS for India

OSS removes bad proprietary software. Look for example to what happened to the compiler market after gcc came around in the '80s. Some compilers could handle the competition, but most just went away. As a customer, the choices become easier when there are fewer choices.

June 12, 2005


MySQL Case Study - 30

I am trying to get "default" value for every ID of JOIN if ON clause were unsucessfull.
In my example, I am trying to get "english" rows, but if they are not available, I would like to get default - "estonian".
I have two tables ie.


id - lang - desc
1 - english - EN_A
1 - estonian - ES_A
2 - english - EN_B
2 - estonian - ES_B
3 - estonian - ES_C

now if I join these two tables ie.:
SELECT * FROM table1 LEFT JOIN table2
ON AND table2.lang ='english'

I am getting:
id - id - lang - desc
1 - 1 - english - EN_A
2 - 2 - english - EN_B

But I would to have "default" row if english is not available:

id - id - lang - desc
1 - 1 - english - EN_A
2 - 2 - english - EN_B
3 - 3 - estonian - ES_C

I have tried with UNION of two JOINS (one for english, one for estonian) but then I am getting
duplicate rows with ID=(1,2)


MySQL Case Study - 29

I need to make an advanced SQL statement. list each patient's name (sorted alphabetically), and the date of their earliest interview that was after 07/01/2003, and the score of the interview on that date. If there were two interviews on that date, list the one with the higher score.

So the output should look like:

PatientName EarliestDate BestScore
-------------------- ------------------------------ -----------
Adams 07/05/2003 60
Bryson 07/03/2003 68
Caldwell 10/03/2003 55

now the schema of the table is
CREATE TABLE tmpPatient(PatientID int, PatientName varchar(20))
CREATE TABLE tmpInterview(InterviewID int, PatientOID int, InterviewDate
datetime, InterviewScore int)

the values of the respective tables is
INSERT tmpPatient (PatientID, PatientName) VALUES (1, 'Adams')
INSERT tmpPatient (PatientID, PatientName) VALUES (2, 'Bryson')
INSERT tmpPatient (PatientID, PatientName) VALUES (3, 'Caldwell')

INSERT tmpInterview (InterviewID, PatientOID, InterviewDate,
InterviewScore) VALUES (1, 1, '2/3/2003', 75)
INSERT tmpInterview (InterviewID, PatientOID, InterviewDate,
InterviewScore) VALUES (2, 1, '7/5/2003', 60)
INSERT tmpInterview (InterviewID, PatientOID, InterviewDate,
InterviewScore) VALUES (3, 1, '12/3/2003', 65)
INSERT tmpInterview (InterviewID, PatientOID, InterviewDate,
InterviewScore) VALUES (4, 2, '7/3/2003', 68)
INSERT tmpInterview (InterviewID, PatientOID, InterviewDate,
InterviewScore) VALUES (5, 2, '7/3/2003', 65)
INSERT tmpInterview (InterviewID, PatientOID, InterviewDate,
InterviewScore) VALUES (6, 2, '2/3/2003', 78)
INSERT tmpInterview (InterviewID, PatientOID, InterviewDate,
InterviewScore) VALUES (7, 3, '4/3/2003', 65)
INSERT tmpInterview (InterviewID, PatientOID, InterviewDate,
InterviewScore) VALUES (8, 3, '10/3/2003', 55)
INSERT tmpInterview (InterviewID, PatientOID, InterviewDate,
InterviewScore) VALUES (9, 3, '11/3/2003', 75)


MySQL Case Study - 28

I have a table that store different items. For each items, I can attach up to 5 different textual categories. Those categories are free-text, and different columns can have the same values (example below).
I am trying to select the count of each of those categories, regardless of it's position.

The table looks like:
ID - int(11) auto_increment,
Description - varchar(100),
Cat1 - varchar(30),
Cat2 - varchar(30),
Cat3 - varchar(30),
Cat4 - varchar(30),
Cat5 - varchar(30) ... (etc).

Sample data may be:
1, "aaa", "Food", "America", "Cheese", NULL, NULL
2, "bbb", "Drink", "America", "Wines", NULL, NULL
3, "ccc", "Wines", "Drink", NULL, NULL, NULL
4, "ddd", "America", "Food", NULL, NULL

The result I want is
Food - 2
America - 3
Drink - 2
Wines - 2
Cheese - 1

Hope you guys can help (BTW, I'm not too happy with the way the table is
designed, but couldn't think of a better way).


MySQL Case Study - 27

Here is my table.
ID | SubID | Amount1 | Amount2 |
25 1 50 100
25 2 25 75
25 3 100 10
26 1 50 60
26 2 25 75

What I want to do is find the sum of Amount1 and Amount2 from each ID where the Subid is Max()

So I basically want
the sum of ID25, SubID3, (100+10)
the sum of ID26, SubID2, (25+75)

The value I want returned from my select statement is 210


MySQL Case Study - 26

Cross tab?

I have a table of user information like name, country, sex
name country sex
jon usa m
lisa malasia f
tom usa m
arnold india m
david india m
nita usa f

i need the result summary country wise and i.e

country total male female
malasia 1 0 1
india 2 2 0
usa 3 1 2
total 6 3 3 --> this i can manage in the reports so don's bother about the total.

I have been able to get it only group by country , but separating sexes is a toil on me
can any one help?

June 06, 2005


Sum all item prices except one

Columns A:B contains clothing items and their matching price.
How could we create a formula that will sum the prices in column B of all the items except the jacket?

Use the SUMIF function as follows:


Result 175

June 02, 2005


Stock prices from Yahoo!

How about an excel file that will download the current share prices from Yahoo website when you click on "Download Data" button on the sheet?
You can download the original version from here...

You can right click on the "Download Data" button and place it anywhere on the sheet.
And more information can be found here...


Microsoft going open source

Microsoft says that its new XML formats will be royalty-free, so the documents should be easily accessible in other applications that support XML. In addition, the XML files will compress everything via Zip compression technology, which will make typical files 50 percent to 75 percent smaller than they would be in today's default Office formats (.doc for Word, .xls for Excel, and .ppt for PowerPoint).

Office 12 won't be the first version of Office to support XML; Office 2003 can save Word and Excel files in XML formats, and Office XP included an XML spreadsheet format. But adopting XML as the default is a major change that should improve interoperability with third-party apps that have struggled with today's famously nebulous and poorly documented Office binary file formats. Even though Microsoft will be using its own schemas, Capossela says that the company will make them widely accessible, which in theory should help competitors such as WordPerfect and OpenOffice handle the new formats.

On the other hand, the new formats will almost certainly cause problems for users of applications designed to work with the existing Office default formats--at least until these apps can be updated to support the XML formats.,aid,121104,pg,1,00.asp


June 2001   July 2001   January 2003   May 2003   September 2003   October 2003   December 2003   January 2004   February 2004   March 2004   April 2004   May 2004   June 2004   July 2004   August 2004   September 2004   October 2004   November 2004   December 2004   January 2005   February 2005   March 2005   April 2005   May 2005   June 2005   July 2005   August 2005   September 2005   October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006   January 2007   February 2007   March 2007   April 2007   June 2007   July 2007   August 2007   September 2007   October 2007   November 2007   December 2007   January 2008   February 2008   March 2008   April 2008   July 2008   August 2008   September 2008   October 2008   November 2008   December 2008   January 2009   February 2009   March 2009   April 2009   May 2009   June 2009   July 2009   August 2009   September 2009   October 2009   November 2009   December 2009   January 2010   February 2010   March 2010   April 2010   May 2010   June 2010   July 2010   August 2010   September 2010   October 2010   November 2010   December 2010   January 2011   February 2011   March 2011   April 2011   May 2011   June 2011   July 2011   August 2011   September 2011   October 2011   November 2011   December 2011   January 2012   February 2012   March 2012   April 2012   May 2012   June 2012   July 2012   August 2012   October 2012   November 2012   December 2012   January 2013   February 2013   March 2013   April 2013   May 2013   June 2013   July 2013   September 2013   October 2013   January 2014   March 2014   April 2014   May 2014   July 2014   August 2014   September 2014   October 2014   November 2014   December 2014   January 2015   February 2015   March 2015   April 2015   May 2015   June 2015   July 2015   August 2015   September 2015   January 2016   February 2016   March 2016   April 2016   May 2016   June 2016   July 2016   August 2016   September 2016   October 2016   November 2016   December 2016   January 2017   February 2017   April 2017   May 2017   June 2017   July 2017   August 2017   September 2017   October 2017   November 2017   December 2017   February 2018   March 2018   April 2018   May 2018   June 2018   July 2018   August 2018   September 2018   October 2018   November 2018   December 2018   January 2019   February 2019   March 2019   April 2019   May 2019   July 2019   August 2019   September 2019   October 2019   November 2019   December 2019   January 2020   February 2020   March 2020   April 2020   May 2020   July 2020   August 2020   September 2020   October 2020   December 2020   January 2021   April 2021   May 2021   July 2021   September 2021   March 2022   October 2022   November 2022   March 2023   April 2023   July 2023   September 2023   October 2023   November 2023   April 2024   May 2024   June 2024  

This page is powered by Blogger. Isn't yours?