SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

gariig: Dec 31, 2004; Beaten into submission by my fiance; Pillbug

Agrikk posted:

How do I do the check on either KillerName or VictimName?

I would try something like

code:

select count(distinct matchID) matches,
       KillerName
from 
	(select matchid, KillerName from table
		UNION
	 select matchid, VictimName from table)
	 as players
group by KillerName

The inner select with the union is splitting your table into different sets those who are in a match and killed someone and those in a match who were killed. The rest of this was based off Jethro's example.

The one problem I see is if someone played a game but didn't kill/wasn't killed in the match they wouldn't have a record in here.

# ? Jun 17, 2009 16:38

Adbot: ADBOT LOVES YOU

# ? May 13, 2024 22:29

FasterThanLight: Mar 26, 2003

Stupid and probably easy question, I just don't know the proper terminology to google it.

In MySQL, I can do a query on two "copies" of the same table:

code:

select A.columnname, B.columnname FROM tablename A, tablename B

I'm trying to do this in postgresql (which I've never used before) but it gives me error saying A.columnname does not exist. How would I do this in postgres?

# ? Jun 17, 2009 18:42

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

That works for me in PostgreSQL. Are you sure you've spelt the columnname correctly? If the column name is the same as a Postgres keyword, try putting it in quotes (e.g. "A.columnname")

# ? Jun 17, 2009 19:06

FasterThanLight: Mar 26, 2003

That was it, thanks! Not the first time I've had quotes mess me up today.

# ? Jun 17, 2009 19:20

Morpheus: Apr 18, 2008; My favourite little monsters

Okay so I was wondering if there was a way to put a 'where' statement in a 'sum' statement. I need to sum up some numbers in a table multiple times, based on a condition. Here is a simplified version of the table:

code:

 PROVINCE | TYPE | AMOUNT 
-------------------------
 ONT      |  A   |  1
 ONT      |  B   |  6
 ONT      |  C   |  3
 NFL      |  B   |  2
 ALB      |  C   |  5
 ALB      |  A   |  7
 MAN      |  B   |  4
 ...

Alright, so what I need is a result that has provinces as the columns, and item types as the rows, with amounts as the cells:

code:

TYPE | ONT | NFL | ALB | MAN | ...
A    |  1  |  2  |  7  |  0
B    |  6  |  0  |  0  |  4
C    |  3  |  0  |  5  |  0

Now one option is to make a table for each province (all 13), but this seems a little needless, and I'd rather not have 13 subqueries, each with subqueries of their own (getting the first table takes a little work). So I was wondering if there some way to put a condition within the sum, and just repeat that conditional 13 times, ie "sum AMOUNT if PROVINCE='ONT', sum AMOUNT if PROVINCE='MAN', etc.'

Ninja Edit: Actually, I looked on the internets and found some useful info (whoud'a thunk). Does this look viable (don't have access to the DB at the moment):

code:

sum(decode(table.PROVINCE,'ONT',table.AMOUNT,0)) as ONT_TOTAL,
sum(decode(table.PROVINCE,'NFL',table.AMOUNT,0)) as NFL_TOTAL
...

# ? Jun 18, 2009 01:19

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I don't know anything about "decode", but this is how I think I would do it

code:

SELECT Type,
       SUM(CASE Province WHEN 'ONT' THEN Amount ELSE 0) AS ONT,
       SUM(CASE Province WHEN 'NFL' THEN Amount ELSE 0) AS NFL,
       ...
       SUM(CASE Province WHEN 'MAN' THEN Amount ELSE 0) AS MAN
FROM Table
GROUP BY Type

This has maintainability issues re: what happens if new provinces are created, existing provinces merge etc. You'd have to edit all your queries.

# ? Jun 18, 2009 01:32

Bad Titty Puker: Nov 3, 2007; Soiled Meat

If you are using Oracle 11g then you can use the new PIVOT feature.

# ? Jun 18, 2009 02:15

Morpheus: Apr 18, 2008; My favourite little monsters

Hammerite posted:

I don't know anything about "decode", but this is how I think I would do it
code:
SELECT Type,
       SUM(CASE Province WHEN 'ONT' THEN Amount ELSE 0) AS ONT,
       SUM(CASE Province WHEN 'NFL' THEN Amount ELSE 0) AS NFL,
       ...
       SUM(CASE Province WHEN 'MAN' THEN Amount ELSE 0) AS MAN
FROM Table
GROUP BY Type
This has maintainability issues re: what happens if new provinces are created, existing provinces merge etc. You'd have to edit all your queries.

With the exception of Nunavut's creation in the previous century, I don't really think this is going to be too much of a problem.

(We're talking about provinces of Canada, incidentally)

But what you're saying is exactly what I needed to know, the ability to use conditionals in the SUM statement. Thanks!

# ? Jun 18, 2009 02:16

Cheesemaster200: Feb 11, 2004; Guard of the Citadel

I have a web program in php/mysql that I use to keep track of raids for a wow guild.

Currently attendance is calculated by tallying the raids attended per member and dividing it by the total raids overall (mysql):

code:

SUM(IF(DATE_SUB(CURDATE(), 
INTERVAL 7 * {$RMS_CONFIG['participation_weeks']} DAY) <= r.raid_date AND r.member_count > 
{$RMS_CONFIG['participation_members']} AND (r.raid_rpp + r.raid_irpp) > 0, 1, 0)) as member_raidcount

However, the problem with this is that there may be multiple raids per day and the people I have been writing this program for want attendance to be daily. Therefore, I essentially want to the above SUM to only sum up dates which are distinct. This way if there are 5 events on one day, it only counts one of them in the summation for the attendance calculation.

I have been trying to figure out how to do this, and the DISTINCT tag doesn't seem to be helping me (or I am using it wrong).

Thanks!

PS, why the hell is the code tag freaking out?

# ? Jun 19, 2009 22:21

Meganiuma: Aug 26, 2003

I'm looking to begin learning some database theory, and I've been trying to find some good introductory books on the subject. I've never really had much interactions with databases, so something that's a fairly gentle introduction would be nice. Do any of you guys have any suggestion? Is there a quintessential introductory book?

# ? Jun 21, 2009 01:51

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Meganiuma posted:

I'm looking to begin learning some database theory, and I've been trying to find some good introductory books on the subject. I've never really had much interactions with databases, so something that's a fairly gentle introduction would be nice. Do any of you guys have any suggestion? Is there a quintessential introductory book?

I think that the Manga Guide to Databases is a good gentle introduction to the concepts.

SQL and relational basics by Fabian Pascal is also good.

# ? Jun 21, 2009 06:29

Squashy Nipples: Aug 18, 2007

Cheesemaster200 posted:

However, the problem with this is that there may be multiple raids per day and the people I have been writing this program for want attendance to be daily. Therefore, I essentially want to the above SUM to only sum up dates which are distinct. This way if there are 5 events on one day, it only counts one of them in the summation for the attendance calculation.

I have been trying to figure out how to do this, and the DISTINCT tag doesn't seem to be helping me (or I am using it wrong).

I don't know MySQL, so I can't comment on the code specifically, but the basic way to SUM different periods is to GROUP BY your period. Since you want daily, thats a piece of cake; just GROUP BY your date field (cut off the time if its a timestamp). I'm not sure if I understand your query, but you'll probably need to group by user too.

camels posted:

I think that the Manga Guide to Databases is a good gentle introduction to the concepts.

SQL and relational basics by Fabian Pascal is also good.

Awesome! My knowledge of DB management is basically non-existent. I will buy both.

# ? Jun 21, 2009 15:27

MoNsTeR: Jun 29, 2002

If you're counting total raids as something like

code:

count(raids.raid_id)

then to instead count days raided, substitute something like

code:

count(distinct trunc(raids.raid_date))

Trunc() is an oracle function, I'm not sure what the MySQL equivalent is. All it does is take a date field with a value like '22-JUN-2009 08:48' and cut it off to '22-JUN-2009'.

# ? Jun 22, 2009 15:48

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

MoNsTeR posted:

Trunc() is an oracle function, I'm not sure what the MySQL equivalent is. All it does is take a date field with a value like '22-JUN-2009 08:48' and cut it off to '22-JUN-2009'.

DATE()

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date

# ? Jun 22, 2009 17:36

Cheesemaster200: Feb 11, 2004; Guard of the Citadel

Dr.Khron posted:

I don't know MySQL, so I can't comment on the code specifically, but the basic way to SUM different periods is to GROUP BY your period. Since you want daily, thats a piece of cake; just GROUP BY your date field (cut off the time if its a timestamp). I'm not sure if I understand your query, but you'll probably need to group by user too.

Awesome! My knowledge of DB management is basically non-existent. I will buy both.

The problem is it is part of a much larger query. If I use DISTINCT right after the SUM or COUNT, I will get the count of distinct outputs if the IF statement (ie. 1). What I need to somehow do is add a conditional in the IF statement that will not count the same date twice.

# ? Jun 22, 2009 18:19

supster: Sep 26, 2003; I'M TOO FUCKING STUPID
TO READ A SIMPLE GRAPH

Are there any inherit long-term problems with constantly creating and deleting a set of tables in MySQL?

I am basically taking an existing application and trying to give each user session their own sandbox by creating their own copy of the database (~15 tables, couple dozen rows each) each time a session is started (and performing gc on expired sessions to delete the unused tables).

I understand this is not the best approach, but I am trying to minimize development time and this is the quickest implementation I could come up with. Support for no more than a dozen concurrent users is necessary. I know there are applications that create and delete thousands of tables (Wordpress MU comes to mind), so I am not too wary, but are there any potential issues I am not considering?

supster fucked around with this message at 00:11 on Jun 25, 2009

# ? Jun 25, 2009 00:08

stuph: Aug 31, 2004; donkey punching my way to the top

supster posted:

Are there any inherit long-term problems with constantly creating and deleting a set of tables in MySQL?

I am basically taking an existing application and trying to give each user session their own sandbox by creating their own copy of the database (~15 tables, couple dozen rows each) each time a session is started (and performing gc on expired sessions to delete the unused tables).

I understand this is not the best approach, but I am trying to minimize development time and this is the quickest implementation I could come up with. Support for no more than a dozen concurrent users is necessary. I know there are applications that create and delete thousands of tables (Wordpress MU comes to mind), so I am not too wary, but are there any potential issues I am not considering?

Hell, if it's small enough, just create temporary tables in memory for the users and let MySQL destroy them when the session ends. No issues that I can really think of, but I'd probably go the create temporary table ... engine = memory route here.

# ? Jun 25, 2009 03:44

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Cheesemaster200 posted:

I have a web program in php/mysql that I use to keep track of raids for a wow guild.

Currently attendance is calculated by tallying the raids attended per member and dividing it by the total raids overall (mysql):
code:
SUM(IF(DATE_SUB(CURDATE(), 
INTERVAL 7 * {$RMS_CONFIG['participation_weeks']} DAY) <= r.raid_date AND r.member_count > 
{$RMS_CONFIG['participation_members']} AND (r.raid_rpp + r.raid_irpp) > 0, 1, 0)) as member_raidcount
However, the problem with this is that there may be multiple raids per day and the people I have been writing this program for want attendance to be daily. Therefore, I essentially want to the above SUM to only sum up dates which are distinct. This way if there are 5 events on one day, it only counts one of them in the summation for the attendance calculation.

I have been trying to figure out how to do this, and the DISTINCT tag doesn't seem to be helping me (or I am using it wrong).

Thanks!

PS, why the hell is the code tag freaking out?

Here's one possible approach:

1. use DATE() to convert r.raid_date from a datetime to a date value.

2. use a subquery with GROUP BY to get the distinct raid_dates.

3. join or select from the distinct dates subquery.

I'm not very familiar with MySQL, but if it supports CASE..WHEN and COALESCE I would look at using those instead of IF(), if it makes the query simpler and easier to understand.

# ? Jun 25, 2009 05:30

Cheesemaster200: Feb 11, 2004; Guard of the Citadel

camels posted:

Here's one possible approach:

1. use DATE() to convert r.raid_date from a datetime to a date value.

2. use a subquery with GROUP BY to get the distinct raid_dates.

3. join or select from the distinct dates subquery.

I'm not very familiar with MySQL, but if it supports CASE..WHEN and COALESCE I would look at using those instead of IF(), if it makes the query simpler and easier to understand.

I have my dataset acquired by the following sub-query:

code:

(SELECT DISTINCT raid_date FROM rpp_raids WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= raid_date AND member_count > 1 GROUP BY raid_date)

And I have my main query where I want to insert it into:

code:

SELECT 
                COUNT(IF(??) as 30_day_part, 
                m.account_id, m.member_name, m.member_id, c.class_name, r.raid_date,c.class_id, m.member_raidrpp + m.member_itemrpp + m.member_adjustrpp + m.member_irpp AS member_currentrpp, m.member_raidfkp + m.member_itemfkp + m.member_adjustfkp AS member_currentfkp, 
		MAX(IF(r.boss_id <> 0, r.raid_date, NULL)) as last_raid_date, 
		MAX(IF(r.boss_id <> 0, r.raid_id, NULL)) as last_raid_id, 
		MIN(r.raid_date) as first_raid_date, 
		MIN(r.raid_id) as first_raid_id, 
                COUNT(DISTINCT r.raid_id) AS raid_count, 
		COUNT(DISTINCT rl.item_id) AS item_count
		FROM rpp_members m
		INNER JOIN rpp_classes c ON c.class_id = m.class_id
		LEFT JOIN rpp_raid_members rm ON rm.member_id = m.member_id
		LEFT JOIN rpp_raids r ON ( r.raid_id = rm.raid_id AND r.member_count > 1 )
		LEFT JOIN rpp_raid_loot rl ON ( rl.member_id = m.member_id AND rl.raid_id = r.raid_id )
		GROUP BY m.member_id

I want to somehow sum or count where r.raid_date = an entry from the sub-query. I can't seem to figure it out.

# ? Jun 25, 2009 17:24

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Cheesemaster200 posted:

I have my dataset acquired by the following sub-query:

code:

(SELECT DISTINCT raid_date FROM rpp_raids WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= raid_date AND member_count > 1 GROUP BY raid_date)

And I have my main query where I want to insert it into:

code:

SELECT 
                COUNT(IF(??) as 30_day_part, 
                m.account_id, m.member_name, m.member_id, c.class_name, r.raid_date,c.class_id, m.member_raidrpp + m.member_itemrpp + m.member_adjustrpp + m.member_irpp AS member_currentrpp, m.member_raidfkp + m.member_itemfkp + m.member_adjustfkp AS member_currentfkp, 
		MAX(IF(r.boss_id <> 0, r.raid_date, NULL)) as last_raid_date, 
		MAX(IF(r.boss_id <> 0, r.raid_id, NULL)) as last_raid_id, 
		MIN(r.raid_date) as first_raid_date, 
		MIN(r.raid_id) as first_raid_id, 
                COUNT(DISTINCT r.raid_id) AS raid_count, 
		COUNT(DISTINCT rl.item_id) AS item_count
		FROM rpp_members m
		INNER JOIN rpp_classes c ON c.class_id = m.class_id
		LEFT JOIN rpp_raid_members rm ON rm.member_id = m.member_id
		LEFT JOIN rpp_raids r ON ( r.raid_id = rm.raid_id AND r.member_count > 1 )
		LEFT JOIN rpp_raid_loot rl ON ( rl.member_id = m.member_id AND rl.raid_id = r.raid_id )
		GROUP BY m.member_id

I want to somehow sum or count where r.raid_date = an entry from the sub-query. I can't seem to figure it out.

Something like this...?

code:

select /*... */, r.raid_date
from /* ... */, rpp_raids r
/*... */
join (select distinct raid_date from rpp_raids r2 /* ... */) as dts
on dts.raid_date = r.raid_date

# ? Jun 25, 2009 21:01

Lord Purple: Mar 7, 2006; Remove your eyes...

I am trying to use a sqlite database to search for records that are similar but not necessarily exactly the term given. Does anyone know of a good way to fuzzy search in sqlite similar to a Levenshtein distance algorith? Eg if I searched for the term 'England' with a margin of error = 1, it would return both 'Angland' and 'Englend' as well as the original term.

# ? Jun 27, 2009 03:21

Bad Titty Puker: Nov 3, 2007; Soiled Meat

scrabbleship posted:

I am trying to use a sqlite database to search for records that are similar but not necessarily exactly the term given. Does anyone know of a good way to fuzzy search in sqlite similar to a Levenshtein distance algorith? Eg if I searched for the term 'England' with a margin of error = 1, it would return both 'Angland' and 'Englend' as well as the original term.

Is it possible to use a spell-checker to scrub the existing data?

Bad Titty Puker fucked around with this message at 09:11 on Jun 28, 2009

# ? Jun 28, 2009 08:13

Lord Purple: Mar 7, 2006; Remove your eyes...

camels posted:

Is it possible to use a spell-checker to scrub the existing data?

Well, I am trying to build something that matches ID3 tags to a database generated by ID3 tags. A lot of the data can't really be spell check because it mostly consists of proper nouns and I can't really ensure the ID3 tags being matched to the database are spelled correctly either. Is there really nothing out there like this? I figured it would be somewhat common in using databases to search for results.

# ? Jun 29, 2009 01:39

var1ety: Jul 26, 2004

scrabbleship posted:

Well, I am trying to build something that matches ID3 tags to a database generated by ID3 tags. A lot of the data can't really be spell check because it mostly consists of proper nouns and I can't really ensure the ID3 tags being matched to the database are spelled correctly either. Is there really nothing out there like this? I figured it would be somewhat common in using databases to search for results.

A lovely and non-scalable way of doing this would be to use the underscore (_) wildcard character in a LIKE statement, permutating over all possible combination relative to your margin of error. So, with England and a margin of error of 1:

_ngland
e_gland
en_land
eng_and
engl_nd
engla_d
englan_

Maybe a sqlite plugin exists to do fuzzy matching? I saw on the fulltext sqlite page that it does not support this natively.

# ? Jun 29, 2009 02:03

Bad Titty Puker: Nov 3, 2007; Soiled Meat

scrabbleship posted:

Well, I am trying to build something that matches ID3 tags to a database generated by ID3 tags. A lot of the data can't really be spell check because it mostly consists of proper nouns and I can't really ensure the ID3 tags being matched to the database are spelled correctly either. Is there really nothing out there like this? I figured it would be somewhat common in using databases to search for results.

Maybe build a lookup table if the number of distinct tags is small, and the margin of error is small? Select all the distinct tags into a dictionary table, maybe use compression and/or pruning to reduce the table size. Then populate a lookup table, something like:

search_keys(word1_id INT, word2_id INT, lev_distance INT)
unique index on (word1_id, word2_id)
index on (word1_id, lev_distance)

I have never used SQLite but I'm thinking that a search_keys table could easily hold hundreds of millions of rows and perform fast.

# ? Jun 29, 2009 03:20

Wonderbread 2000: Oct 27, 2008

I've got two tables which are joined by a third to avoid a many-to-many relationship. Specifically, a User can belong to many Facilities. I need to display various things about users, including the facilities they belong to, on one line of a gridview. Is there any way to build a view or something in which all of the facilities to which a user belongs are concatenated into one column?

# ? Jun 29, 2009 20:52

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

What database are you using? If you're using Postgres you can use something like:

code:

SELECT u.user_name, ARRAY(SELECT f.name
                        FROM facilities f
                           JOIN user_facilities uf USING (facility_id)
                        WHERE uf.user_id=u.user_id)
FROM users u;

# ? Jun 29, 2009 22:26

MoNsTeR: Jun 29, 2002

Neat that Postgres provides a built-in to do that.

If you happen to be on Oracle I can share my solution, which is a kludgy User-Defined Aggregate Function.

# ? Jun 29, 2009 23:29

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

Wonderbread 2000 posted:

I've got two tables which are joined by a third to avoid a many-to-many relationship. Specifically, a User can belong to many Facilities. I need to display various things about users, including the facilities they belong to, on one line of a gridview. Is there any way to build a view or something in which all of the facilities to which a user belongs are concatenated into one column?

If you are using MySQL look at GROUP_CONCAT

# ? Jun 30, 2009 15:27

Atoramos: Aug 31, 2003; Jim's now a Blind Cave Salamander!

Is there a way of directly importing an excel file into a database, or copying over the rows?

Say I have a SQL table set up like this:

code:

KEY   NAME   CLASS
01    BOB    STUDENT
02    JIM    TEACHER


and then I have an excel file like this:

03   DAN     STUDENT
04   MATT    STUDENT

can I copy that over easily?

# ? Jun 30, 2009 18:47

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

DBs usually have bulk import tools that can read from CSV files... But I wouldn't say they're easy like "accomplishable under 3 minutes" easy. You set up the schema, export to CSV, align all the columns, set it to load, and then pray.

Really if this is a one shot job you're better off exporting to CSV, writing a Perl script to generate insert statements, and then calling them. I would consider bulk import to be more of a productionalized solution.

# ? Jun 30, 2009 18:50

Atoramos: Aug 31, 2003; Jim's now a Blind Cave Salamander!

I just want to get used to the import tool from now. Is there a guide to doing it through SQL Server Management Studio?

# ? Jun 30, 2009 19:00

Atoramos: Aug 31, 2003; Jim's now a Blind Cave Salamander!

I just found out about the SQL command 'BULK INSERT' which is almost perfect, however with a comma delimited file, I give it the FIELDTERMINATOR of ',' and it dies trying to handle "this text has, a comma". Any idea how to get around that? Do I need to use another type of delimiter?

Edit:

BULK INSERT EDIT_LONG_CODE
FROM 'C:\ImportTest.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)

This is just about the fastest thing I could have hoped for, if anyone else ends up needing this. Just pop it onto a tab delimited file and you're good to go.

Atoramos fucked around with this message at 19:40 on Jun 30, 2009

# ? Jun 30, 2009 19:20

Squashy Nipples: Aug 18, 2007

Atoramos posted:

Do I need to use another type of delimiter?

No, I think you need to encapsulate your text data properly.
http://en.wikipedia.org/wiki/Comma-separated_values#Specification

code:

Example

1997,Ford,E350,"ac, abs, moon",3000.00
1999,Chevy,"Venture ""Extended Edition""","",4900.00
1996,Jeep,Grand Cherokee,"MUST SELL!
air, moon roof, loaded",4799.00

This CSV example illustrates that:

fields that contain commas, double-quotes, or line-breaks must be quoted,
a quote within a field must be escaped with an additional quote immediately preceding the literal quote,
space before and after delimiter commas may be trimmed (which is prohibited by RFC 4180), and
a line break within an element must be preserved.

I sometimes use Access as a kludge for Excel data... I have an ODBC connection to one of my real DBs, so instead of making a CSV file and/or schema, I paste it into a temp Access table from Excel, and then I can just insert the data directly into it.

Squashy Nipples fucked around with this message at 21:27 on Jun 30, 2009

# ? Jun 30, 2009 21:06

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I'm sure I'm being retarded, but can someone point out to me what's wrong with this query?

code:

SELECT CASE NumPlays
            WHEN 0 THEN 0
            ELSE ROUND(10000*POW((1-1/NumPlays), 2)*NumWins/NumPlays)
            END
            AS Rating
       FROM (
           SUM(
               CASE Game.NumberOfPlayers
                    WHEN 2 THEN 0.8
                    ELSE 1
                    END
           ) AS NumPlays,
           SUM(
               CASE WHEN PlayerGameRcd.GameResult = 'Finished 1st'
                        AND Game.NumberOfPlayers = 2
                        THEN 0.8
                    WHEN PlayerGameRcd.GameResult = 'Finished 1st'
                        AND Game.NumberOfPlayers > 2
                        THEN 1
                    WHEN PlayerGameRcd.GameResult = 'Finished 2nd'
                        AND Game.NumberOfPlayers = 3
                        THEN 0.44
                    WHEN PlayerGameRcd.GameResult = 'Finished 2nd'
                        AND Game.NumberOfPlayers = 4
                        THEN 0.63
                    WHEN PlayerGameRcd.GameResult = 'Finished 3rd'
                        AND Game.NumberOfPlayers = 4
                        THEN 0.26
                    ELSE 0
                    END
           ) AS NumWins
           FROM PlayerGameRcd
               LEFT JOIN Game
               ON PlayerGameRcd.Game = Game.GameID
           WHERE PlayerGameRcd.User = 1           /*(any number)*/
               AND PlayerGameRcd.GameResult <> 'Game Aborted'
               AND PlayerGameRcd.GameCounts = 1
               AND Game.Friendly = 0
       ) AS QRYA

I get the following:

MySQL posted:

MySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM( CASE Game.NumberOfPlayers WHEN 2 THEN ' at line 7

# ? Jul 2, 2009 23:27

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

What's that FROM clause doing on line 6?

# ? Jul 2, 2009 23:30

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Ah poo poo, I've forgotten to put the word SELECT at the start of my subquery. I knew it would be something retarded. Thanks.

# ? Jul 2, 2009 23:32

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I was going to post again puzzling over the fact that my query didn't seem to be working in cases where there are no rows over which to sum. It didn't seem to be giving me back an answer. I worked out after a bit of puzzling that it was giving back an answer, but the method I was using to view the output was fooling me into thinking that it wasn't. It was just giving back NULL. It comes down to the fact that SUMming over zero rows doesn't give you zero in SQL (well, MySQL anyway); it gives you NULL. I find this rather annoying; it seems ridiculous to me (as a mathematician) that an empty sum should be asserted to be something other than zero. This rant isn't addressed to anyone in particular; carry on.

# ? Jul 3, 2009 00:14

Squashy Nipples: Aug 18, 2007

Dude, don't dis NULL, its super useful. Sometimes the difference between a zero value and no data is an important distinction.

I ran afoul of NULL a few weeks ago, when I needed the SUM of a set of values to return NULL if ANY of the individual values were NULL. Check back a few pages.

# ? Jul 3, 2009 00:55

Adbot: ADBOT LOVES YOU

# ? May 13, 2024 22:29

Vanadium Dame: May 29, 2002; HELLO I WOULD LIKE TO TALK TO YOU ABOUT MY VERY STRONG OPINIONS

Dr.Khron posted:

Dude, don't dis NULL, its super useful. Sometimes the difference between a zero value and no data is an important distinction.

I ran afoul of NULL a few weeks ago, when I needed the SUM of a set of values to return NULL if ANY of the individual values were NULL. Check back a few pages.

NULL can be useful, just remember that it's strange, at least in Oracle. 1 = NULL and 1 != NULL both evaluate as false for one thing, and for another you can't index on null. If you've got a column with an index on it and there are null values in there you can use that index if you specify <column value> = <whatever>, but not <column value> IS NULL;

EDIT: and I just used a ; instead of a . at the end of a sentence, I've been doing this poo poo for too long

# ? Jul 3, 2009 01:00

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »