Sometimes you want to count the number of results which meet a certain condition. Excel has a COUNTIF(…) function but there is nothing like that in the (current) SQL standard. The solution is to mix the SUM function with a CASE statement. Confused? Let’s see an example. Imagine you have two tables – student (student_id, [...]
Posts Tagged ‘MySQL’
Conditional COUNT in SQL
Posted: 10th October 2010 by Tim in SQLTags: count, COUNTIF, if, mssql, MySQL, oracle, PostgreSQL, query, sql, sql server, statement, sum
2
DESCRIBE table in PostgreSQL
Posted: 20th January 2010 by Tim in PostgreSQLTags: describe, detail, MySQL, sql, syntax, table
MySQL has a nice way to get the details of a table structure through the DESCRIBE command. PostgreSQL can also do this for you with \d table_name. For example (sorry about the poor layout): tim=# \d test_table Table “public.test_table” Column | Type | Modifiers ———+—————————–+——————- test_id | integer | not null default nextval(‘test_table_test_id_seq’::regclass) name | [...]
SHOW FULL PROCESSLIST in PostgreSQL
Posted: 21st December 2009 by Tim in MySQL, PostgreSQLTags: activity, MySQL, PostgreSQL, processlist, stats
Like MySQL, there is a way to see all processes in PostgreSQL. It’s one simple command: SELECT * FROM pg_stat_activity; Note that if the current_query field keeps coming up empty, you might need to enable stats_command_string in your postgresql.conf. Enjoy.
USE [schema_name] in PostgreSQL
Posted: 17th November 2009 by Tim in PostgreSQLTags: database, MySQL, PostgreSQL, select, SET, USE
For those moving from MySQL to PostgreSQL, there are a few differences which you will need to get used to. One of these differences is the USE command, used to select the schema to select tables, views, etc from. PostgreSQL does not have the USE command. Instead, you can use: SET search_path TO [schema_name] For [...]
Connecting to MySQL in C
Posted: 20th August 2009 by Tim in C, MySQL, UbuntuTags: C, coding, compile, connectivity, database, development, gcc, MySQL, sockets, sql, Ubuntu
MySQL comes with a library to make talking to MySQL with C easyish. There are a few things you have to install first, though. I’m using Ubuntu 8.04 for this walkthrough, but things should be similar for other flavours of Linux. Before we start, we have to download the development files required: sudo apt-get install [...]
Inline IF and CASE statements in MySQL
Posted: 13th August 2009 by Tim in MySQLTags: case, data, inline, MySQL, query, statement, switch
There are times where running IF statements inside a query can be useful. MySQL provides a simple way to do this through the use of IF and CASE statements. The IF statement takes three arguments; the conditional, the true value and the false value. False and true values may be static values or column values. [...]
MySQL Foreign Keys
Posted: 23rd July 2009 by Tim in MySQLTags: constraints, foreign keys, integrity, MySQL, table
Foreign keys are a good way to ensure consistency between tables, by ensuring that the value in one table column exists in another table. For example, you might have a person table which contains person_id, first_name and surname. You may then have another table, person_ages, which contains person_id and date_of_birth. You would add a foreign [...]