SELECT COUNT( * ) as "Number of Rows"FROM orders WHERE ord_amount>1500;Note that the word TABLE is not there. OpenOffice Base complains about the absence of TABLE and says syntax error with the word TABLE in there. The query wizard is non-functional; conditions do not generate WHERE statements. Some of you suggested MySQL, but I cannot find a download that isn't an upgrade only version.
I will try the LibreOffice variant next.
Clayton,
ReplyDeletego here https://dev.mysql.com/downloads/windows/installer/8.0.html and scroll down to the "generally available (GA) Releases" section, and click one of the two links.
The first one, at 15.9MB, is just the installer. It will ask you what components you want, and then download just them. The other one, at 273.4M, has everything.
Clayton:
ReplyDeleteDo you have a linux computer available? Installing mysql is effortless in linux.
Common 'real' database engines available are mariadb (mysql was acquired by Oracle and the original developers forked it into mariadb). You might also look at postgresql, as that is another very capable open source database engine.
What is your data source? If it is a spreadsheet or the like, I can probably come up with a database design and insert statements for you (I work with SQL in my day job).
Feel free to email me at the follow-up email address if I can give you a hand on that.
Wayne
Wayne: I have a Debian Linux variant running in VMWare Player. Sharing data with my Windows file system is not easy, for reasons that elude me. I successfully imported the spreadsheet into OpenOffice Base. I have discovered some fields that should be INT are TEXT. In OpenOffice Base, I was able to use ALTER TABLE... ALTER COLUMN to change field types. But OO Base is severely broken. LibreOffice Base can import OO Base database, but in LO Base:
ReplyDeleteAlter table "MassMurder" alter column "dead victim count" INT
gives
1: Wrong data type: java.lang.NumberFormatException: For input string: "?" in statement [alter table "MassMurder" alter column "dead victim count" INT]
Can anyone tell me if the SELECT statement above is correct? If it is, I will abandon OpenOffice and LibreOffice. OO is clearly not running real SQL. LO seems broken the same way.
ReplyDeleteWayne: I did SQL for a living from 2008-2014. I am suspecting that OO and LO have implemented a defective or incomplete SQL. If so, the only choice is MySQL. But I want to make sure that I am giving valid SQL commands before abandoning LibreOffice.
ReplyDeleteSELECT COUNT( * ) as "Number of Rows"FROM orders WHERE ord_amount>1500;
ReplyDeleteShould be:
SELECT COUNT( * ) as "Number of Rows" FROM orders WHERE ord_amount>1500;
(space between the " and the F in FROM.
This assumes orders is the name of the table. I'd also put spaces around the >.
Clayton:
ReplyDeleteThe only change I would suggest is to replace "Number of Rows" with NumberOfRows,avoiding the need to escape it. Escaping column aliases can get annoying.
Honestly, I think you would have an easier time if you change the database architecture to be more like the following:
CREATE TABLE mass_murder (
id INT PRIMARY KEY,
year INT NOT NULL,
month INT NOT NULL,
dead_victim_count INT NOT NULL DEFAULT 0,
injured_victim_count INT NOT NULL DEFAULT 0,
category VARCHAR(64),
cause VARCHAR(64),
weapon VARCHAR(64)
);
You can then update your query to be more like the following:
SELECT
10*ROUND(mm.year * 10,0) AS decade,
COUNT(mm.id) AS total_dead,
SUM(mm.undead_victim_count) AS zombie_count
FROM
mass_murders mm
GROUP BY
10*ROUND(mm.year * 10,0)
ORDER BY
10*ROUND(mm.year * 10,0) ASC;
And for the detail rows:
SELECT
10*ROUND(mm.year * 10,0) AS decade,
mm.weapon AS weapon,
COUNT(mm.id) AS total_dead,
SUM(mm.undead_victim_count) AS zombie_count
FROM
mass_murders mm
GROUP BY
10*ROUND(mm.year * 10,0),
mm.weapon
ORDER BY
10*ROUND(mm.year * 10,0) ASC,
mm.weapon ASC;
If you need a hand migrating a bunch of columnar data to the above format, please let me know. I should be able to parse a CSV using either perl or regexes.
You mention that MySQL is the only other choice. I would honestly look at MariaDB instead of MySQL. I personally prefer postgresql, but both are decent engines. All three are free and available for Windows or Linux.
One point to be aware of is that by default MySQL and MariaDB use backticks (`) to enclose table and column names and postgresql follows ANSI SQL and uses double quotes (").
Wayne