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 »

Code Jockey: Jan 24, 2006; 69420 basic bytes free

yaoi prophet posted:

I can't speak for MS SQL, but on the Oracle side this would sort of work as he expects (although it's still a bad way to do it). The reason is that you have to explicitly commit, and if you don't it's still the same transaction. If you set autocommit on your db driver, then it will automatically call commit on each separate call, so stacking them into one statement would effectively treat them as one transaction.

A far better way to accomplish this same thing is to turn off autocommit, call each one in sequence, and then commit when you are ready.

Now, maybe MS SQL is different in how it handles transactions, I'm not sure if it's the same in this regard. Hopefully someone can clarify.

It does work. :psyduck:

It's a bad way to do it, of course, but... but it does work. I hate to beat the catchphrase into the ground, but my mind is absolutely blown. I didn't think it worked that way, I figured that MS SQL didn't group the queries like that, but... gently caress. I just ran a test where I did 10 inserts then violated the primary key on insert 11, and by god, it rolled it all back.

I am so glad I did this test before approaching him and saying it didn't work. :sweatdrop:

Someday I'll rewrite it, but I guess it isn't as pressing an issue as I thought.

# ? May 2, 2008 18:45

Adbot: ADBOT LOVES YOU

# ? Apr 29, 2024 17:29

npe: Oct 15, 2004

Well, in your defense, it is pretty :downs:

and you may as well crosspost to the Coding WTF's thread. It works only because you're dodging the autocommit by shoving it all on one line. That's just a stupid way to do it.

# ? May 2, 2008 19:02

Code Jockey: Jan 24, 2006; 69420 basic bytes free

yaoi prophet posted:

Well, in your defense, it is pretty and you may as well crosspost to the Coding WTF's thread. It works only because you're dodging the autocommit by shoving it all on one line. That's just a stupid way to do it.

I totally agree. I remember when I first encountered this when I was rewriting a page which created customer records, and I saw like 4 inserts jammed into one line. He told me "Yeah, that basically makes a transaction..."

It was a good preview of things to come. I won't even start into the internal, catch-all, horribly bloated "data access layer" he wrote, which has the fun habit of a) eating exceptions and not returning them, and b) returning null if there were zero results returned from a query or an exception was thrown.

Just let that last line sink in for a second.

Needless to say, I only use it for really, really, basic dumb stuff like dropdown list population.

e. I'll probably hit that Coding WTF thread later, I've got stories.

# ? May 2, 2008 19:13

Filburt Shellbach: Nov 6, 2007; Apni tackat say tujay aaj mitta juu gaa!

I'm writing an application but having trouble describing my dataset relationally.

I have tables for Games and Players. I also have trophies (which are basically a specific order on games), but they don't need to be in the database; I just refer to trophies by name. Since every game causes every trophy to change, I have a TrophyChange table. TrophyChange is decent, and I'll probably keep it, but it makes some views difficult.

I'd like to, for example, display a player's best game for a trophy, with a few games above and below that. To do that, I'd have to order the games for that trophy, then search (linearily? ugh!) for the player, then grab the next few games.

Displaying the trophy changes a player caused is easy enough (it's a left join), but I'd also like to display games that did better, which displaced him in the ranks. I have no idea how to do this efficiently (other than yet another linear scan) Here's what I want:

* You earned 2nd place for fastest game! (10 hours ago)
* Foo earns 1st place for fastest game, you're now 3rd (3 hours ago)
* Bar earns 3rd place for fastest game, you're now 4th (5 minutes ago)

I'm about to go back to in-memory data structures which are much slower and fiddly. Any advice would be greatly appreciated!

# ? May 2, 2008 20:39

chocojosh: Jun 9, 2007; D00D.

Code Jockey posted:

I totally agree. I remember when I first encountered this when I was rewriting a page which created customer records, and I saw like 4 inserts jammed into one line. He told me "Yeah, that basically makes a transaction..."

It was a good preview of things to come. I won't even start into the internal, catch-all, horribly bloated "data access layer" he wrote, which has the fun habit of a) eating exceptions and not returning them, and b) returning null if there were zero results returned from a query or an exception was thrown.

Just let that last line sink in for a second.

Needless to say, I only use it for really, really, basic dumb stuff like dropdown list population.

e. I'll probably hit that Coding WTF thread later, I've got stories.

Please do, I'd love to see them

# ? May 2, 2008 20:50

npe: Oct 15, 2004

Sartak posted:

I'm about to go back to in-memory data structures which are much slower and fiddly. Any advice would be greatly appreciated!

Are you using Oracle by any chance?

# ? May 2, 2008 20:59

Filburt Shellbach: Nov 6, 2007; Apni tackat say tujay aaj mitta juu gaa!

yaoi prophet posted:

Are you using Oracle by any chance?

No. I use SQLite for development, Postgres for production. If I have to depend on Postgres-only features, that'd be OK.

# ? May 2, 2008 21:02

npe: Oct 15, 2004

Was worth a shot. This is the sort of thing that analytic functions (sometimes called windowing functions) are meant to do, but I don't know if postgres does much in that area.

# ? May 2, 2008 21:05

Code Jockey: Jan 24, 2006; 69420 basic bytes free

Real quick general question, not specific to MS SQL.

I had heard at one point that specifying a number of returned records is faster than just doing a select, in cases such as knowing, say, there'll be no more than a few thousand and doing a select top 10000. Now ignoring the "you might not know how many are actually there and might miss some", is it faster to explicitly state the max number of rows to return? If I had a non-specific query return 400 rows, and I ran that query specifying I only wanted 400, would the specific query return faster?

# ? May 2, 2008 22:53

var1ety: Jul 26, 2004

Code Jockey posted:

Real quick general question, not specific to MS SQL.

I had heard at one point that specifying a number of returned records is faster than just doing a select, in cases such as knowing, say, there'll be no more than a few thousand and doing a select top 10000. Now ignoring the "you might not know how many are actually there and might miss some", is it faster to explicitly state the max number of rows to return? If I had a non-specific query return 400 rows, and I ran that query specifying I only wanted 400, would the specific query return faster?

You can't say it would always be faster, because there are a lot of "it depends".

With a "top N" query you need to sort before you can return rows, but if you know you only want 400 results the database can use less sort space (only need to keep a window of the top 400, instead of sorting every row returned), which would make the query more efficient, and faster with very large result sets because you would not need to use temp space.

If your query is doing a full table scan it could be faster because it could stop earlier, and it could similarly end earlier with some indexed access paths.

Oracle can, based on statistics, shuffle around some joins and access paths based on the FIRST_ROWS_N (N=1,10,100,1000) hint.

# ? May 3, 2008 01:00

leinad: Jan 14, 2003

A design question that hopefully hasn't been discussed yet: is it bad form or bad for performance to have a table with lots of fields (in MySQL)? "Lots" is pretty relative term. I'm talking around 20-25 different fields, maybe more, in my case. I am making a site that has user accounts, so in addition to storing account information for each user, I want to store personal information like a photo, birthday, and lots of other random information. Is there any point where it's best to split up the table into two?

# ? May 3, 2008 18:08

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

leinad posted:

A design question that hopefully hasn't been discussed yet: is it bad form or bad for performance to have a table with lots of fields (in MySQL)? "Lots" is pretty relative term. I'm talking around 20-25 different fields, maybe more, in my case. I am making a site that has user accounts, so in addition to storing account information for each user, I want to store personal information like a photo, birthday, and lots of other random information. Is there any point where it's best to split up the table into two?

You split up a table for normalization when needed. There is nothing wrong with having lots of fields, as long as each field depends on the primary key, the whole key, and nothing but the key (3rd normal form). Splitting up records just because you have a lot of fields can actually slow you down since you now need to do joins. Also, 25 is not a lot of fields, I have made tables with 70+ fields and have seen tables with over 200 fields that made sense. As long as the database is normalized then it should be good.

However, if you have a lot of fields then using SELECT * everywhere is not always a good idea, its usually best to only select the fields you need.

# ? May 4, 2008 17:14

leinad: Jan 14, 2003

Begby posted:

You split up a table for normalization when needed. There is nothing wrong with having lots of fields, as long as each field depends on the primary key, the whole key, and nothing but the key (3rd normal form). Splitting up records just because you have a lot of fields can actually slow you down since you now need to do joins. Also, 25 is not a lot of fields, I have made tables with 70+ fields and have seen tables with over 200 fields that made sense. As long as the database is normalized then it should be good.

However, if you have a lot of fields then using SELECT * everywhere is not always a good idea, its usually best to only select the fields you need.

Thanks. That's pretty much what I expected to hear. It is still in 3NF.

Also, thanks for the tip about SELECT *. I was never sure if it was easier for the DB to simply return everything or to only pick out necessary fields.

# ? May 4, 2008 17:53

Grigori Rasputin: Aug 21, 2000; WE DON'T NEED ROME TELLING US WHAT TO DO

I'm pretty new to MySQL and am wondering how best to enforce a set of data rules at the table level. The basic idea is that I want to link like data together, but never repeat a combination of it. I can deal with it in code, but I'd rather prevent it as low as possible.

Assume the following table:
first_id INT
second_id INT

the following rules:
1. first_id and second_id cannot be equal.
2. No combination of first_id and second_id can repeat. For example, you can't have the following records:

code:

first_id   | 0 | 1
second_id  | 1 | 0

I'm trying to link records in a table to records in the same table and never want an item to link to itself. How do I do it? Is there a better structure?

# ? May 5, 2008 23:21

var1ety: Jul 26, 2004

Grigori Rasputin posted:

I'm pretty new to MySQL and am wondering how best to enforce a set of data rules at the table level. The basic idea is that I want to link like data together, but never repeat a combination of it. I can deal with it in code, but I'd rather prevent it as low as possible.

Assume the following table:
first_id INT
second_id INT

the following rules:
1. first_id and second_id cannot be equal.
2. No combination of first_id and second_id can repeat. For example, you can't have the following records:
code:
first_id   | 0 | 1
second_id  | 1 | 0
I'm trying to link records in a table to records in the same table and never want an item to link to itself. How do I do it? Is there a better structure?

It sounds like you want to store and enforce integrity on a directed acyclic graph in the database. Here's a good Google result talking about DAGs and databases:

http://69.10.233.10/KB/database/Modeling_DAGs_on_SQL_DBs.aspx

I didn't read the whole article, but you could handle (1) with a direct BEFORE INSERT trigger, and you could handle (2) in a similar fashion by making sure that when a user tries to add edge e' (n->n') to an existing DAG G that edge e'' (n'->n) is not in the transitive closure of G (which would be pre-computed and stored in a table). This table would grow quite large in the case that you had a large number of vertices.

If an edge e' introduces a cycle you would be able to tell very quickly by examining the transitive closure of G and raising an error. Inserting edges that do not introduce cycles (or removing edges) would be at worst O(V^3), where V is the number of vertices in G (in the case you choose to fully recompute it after each operation).

You'd be doing this in code in a similar fashion anyway, and it will be faster in the database since you will have persistent, pre-computed information about your graphs, but it's probably going to be easier to keep the logic in your code.

# ? May 6, 2008 01:23

indigoe: Jul 29, 2003; gonna steal the show, you know it ain't no crime

I have a massive database of logs, and I need to be able to make some sense of the data stored within. At the moment it has around 200 million rows and around 30 GB in size, including the index. Speaking of which, it has an index on a single column (date, yyyy-mm-dd format) which alone is 5 GB. This is a MySQL 4 database.

I have various queries that include various fields in their WHERE clauses, but the one common field is the date field (which is why I indexed it). At the moment the fastest way I can generate a report is grab every single row for a date range and process it in PHP, and do whatever calculations and transformations needed. This sort of works as long as the number of rows doesn't exceed 1.5-2 million, after that I guess the server runs out of memory and grinds to a halt. Apart from that it works reasonably well but I can't help feeling there is a better way.

Should I be indexing more fields that are included in my WHERE clauses? I'm concerned I will end up with an index that's near as big as the database itself. Is there some other way to approach this?

# ? May 6, 2008 07:45

TiMBuS: Sep 25, 2007; LOL WUT?

My question is not so much of a specific problem, I'm more looking for some wisdom from someone who knows better.
I've skimmed over the previous posts so I might have missed an answer relevant to my question and if so I will happily take your verbal abuse and click on over to the answer.

Q: How 3d is too 3d? (alt title: CREATE; friend or foe?)
When using flatfiles or database files, I find myself wrapping structures within structures and storing them. For example, I might want to create an array of forum threads, and each element of that array would thus be an array of posts within that thread, and each post would be a hash containing the information in that post.
In Perl:

code:

my $structure = [ 
	[ {poster => "butteman", text => "hurf durf"}, {poster => "manbutt", text => "ffffff"} ],
	[ {...}, ... ],
];

So to get the content of a post, you'd use $structure->[$threadnumber]->[$postnumber]->{'text'}; (or something).
Obviously, with SQL this isn't quite as simple, since tables can't be stored within tables. You'd have to separate your data into tables and link them by ID, I guess. (yes, I wiki'd normal form a few moons ago).
But with the current data layout I've specified, you'd need a table to list the threads, then you'd need to make a new table for every single thread. Isn't that getting excessive? I'm not very SQL savvy, so I'm not sure if this is the 'proper' thing to do. Most of the apps I've ever ripped apart or installed have a set number of tables and rarely ever create more. This apparent trend makes me assume that creating a table is a 'big thing' in the magical world of SQL, and shouldn't be done on a whim. But if I didn't create a table for every thread, then I'd have to resort to making a bigass 'posts' database and just dumping everything in it, which doesn't feel right, nor does it look very pretty. Plus, it makes getting the posts much more annoying, since I'll have to filter them out.

So yeah, for a situation like this, would I just dump everything in one table, or would I make (potentially)thousands of tables? Or, maybe there is some kind of alternative data structure that I haven't thought of? You tell me.
Your answer will probably affect every database I ever make in the future here.

Edit: While I'm firing off big fat generalized questions I should also ask if there are perhaps different databases better suited to different storage approaches. I've only really looked at mySQL/postgre and they seem pretty similar.

TiMBuS fucked around with this message at 09:27 on May 6, 2008

# ? May 6, 2008 09:09

nbv4: Aug 21, 2002; by Duchess Gummybuns

I have a table thats about to be populated with a few hundred thousand records. The table has about 34 columns. Some of these columns are boolean columns that could be combined with one another. For instance one column called "is_car" and another "is_motorcycle", which could be combined as "vehicle_type" with a value of either NULL, "m", or "c".

The way it is now works fine, I'm just wondering if it's worth the trouble to get a little more performance (if any performance at all) by restructuring the table. Will it matter at all in the long run?

# ? May 6, 2008 11:21

chocojosh: Jun 9, 2007; D00D.

TiMBuS posted:

So yeah, for a situation like this, would I just dump everything in one table, or would I make (potentially)thousands of tables? Or, maybe there is some kind of alternative data structure that I haven't thought of? You tell me.
Your answer will probably affect every database I ever make in the future here.

I'm sure some of the very talented DBAs can answer you better than I can. I'm going to take a stab at it though anyways.

First normal form states that all database entries should be atomic. Note that atomic means that it conceptually cannot be broken down further. There should be only --one-- piece of information Strings are considered atomic even though you can extract a substring. You can also combine several related fields as long as there is only

Another approach would be to make N different fields if you assume you can have up to a fixed N of values (e.g. assume you can have up to 10 forum threads and make 10 fields Thread!, Thread2, ... Thread10). This is a quick solution for something small/dirty, but I would not recommend it as a good practice (I used this approach for my last project which was a website that will only need to be updated once a year and I was running late on it).

Why should you avoid composite/non-atomic values? I'm not too sure. I would think it's because the relational model is meant to handle joins much better than it is in handling substrings. It also becomes difficult to enforce referential integrity if the values are non-atomic.

I don't think it's a problem to have 1000s of tables if your problem domain is so big that you'll actually need one thousand. I know at my company our web application (I'd estimate 0.5 M - 1 M LOC) has a few hundred database tables, and it's never been a problem.

# ? May 6, 2008 12:52

npe: Oct 15, 2004

TiMBuS posted:

So yeah, for a situation like this, would I just dump everything in one table, or would I make (potentially)thousands of tables? Or, maybe there is some kind of alternative data structure that I haven't thought of? You tell me.
Your answer will probably affect every database I ever make in the future here.

The correct way is to put all of your posts in one table. Creating individual tables would just be the wrong way to do it. Filtering is simply joining or adding a WHERE clause, and is the normal way of doing things.

Generally, creating tables on the fly is to be avoided in pretty much any database system.

# ? May 6, 2008 12:57

var1ety: Jul 26, 2004

indigoe posted:

Should I be indexing more fields that are included in my WHERE clauses? I'm concerned I will end up with an index that's near as big as the database itself. Is there some other way to approach this?

Indexes are not always good, but a common rule of thumb is that if you are pulling out less than 15% of the rows in the table then evaluating a new index on a column (or group of columns) with a lot of distinct combinations might help. With a table that large any indexes will necessarily be large and cumbersome.

In my own experience I have found that the problem can usually be reduced by pre-computing intermediary values and storing them as additional columns, or in a secondary table. The strategies MySQL has available to it for reporting tasks are very limited, as compared to SQL Server and Oracle, but you want to do as much as you can in the database before you offload it to another system for processing. It will almost *never* be faster to offload calculations to another language if these same calculations can be done directly (through direct SQL or through procedures) in the database.

# ? May 6, 2008 15:37

var1ety: Jul 26, 2004

nbv4 posted:

I have a table thats about to be populated with a few hundred thousand records. The table has about 34 columns. Some of these columns are boolean columns that could be combined with one another. For instance one column called "is_car" and another "is_motorcycle", which could be combined as "vehicle_type" with a value of either NULL, "m", or "c".

The way it is now works fine, I'm just wondering if it's worth the trouble to get a little more performance (if any performance at all) by restructuring the table. Will it matter at all in the long run?

It doesn't matter with small columns like that. If you consolidated them it would just reduce the width of the row in the raw data file by a few bytes.

Make sure you don't consider making an attribute table of (src_id, attrname, attrval) and storing your attributes in a 1:N relationship like that.

# ? May 6, 2008 15:49

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

nbv4 posted:

I have a table thats about to be populated with a few hundred thousand records. The table has about 34 columns. Some of these columns are boolean columns that could be combined with one another. For instance one column called "is_car" and another "is_motorcycle", which could be combined as "vehicle_type" with a value of either NULL, "m", or "c".

The way it is now works fine, I'm just wondering if it's worth the trouble to get a little more performance (if any performance at all) by restructuring the table. Will it matter at all in the long run?

The best way to do this would be to add in another table for vehicle types, the contents of that table would might look like

ID TypeName
1 Car
2 Motorcycle
3 Rocket

Then you would have a vehicle type ID stored in the your vehicle table. If you need a character code you can switch the primary key to be a character code if you want.

Now you have a pretty TypeName so if you had to display a friendly name on the screen for people to see instead of a single character code you wouldn't have to hard code it. You can also add in more info about the vehicle types of needed.

This won't slow your DB down and should be a lot easier to code around than a bunch of booleans. However the database won't perform any faster like this.

# ? May 6, 2008 15:53

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

var1ety posted:

Make sure you don't consider making an attribute table of (src_id, attrname, attrval) and storing your attributes in a 1:N relationship like that.

Why not?

# ? May 6, 2008 15:55

var1ety: Jul 26, 2004

Begby posted:

Why not?

Space is effectively unlimited on databases, but CPU power is not. You should design your database to answer the questions you plan to ask quickly and efficiently.

People model their database like this for a lot of reasons, but it's usually to avoid making concrete design decisions or to get around their DBAs (if it's for space it's an even worse reason).

It's no question that flattening the data to answer simple questions will be faster when that data is pre-flattened. You'll experience decreased concurrency through this system, and complex questions will be even harder for the database to answer quickly. God help you if you're using self joins to answer these questions.

# ? May 6, 2008 17:18

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

My company's web app let's customers run calculations that are Expensive. How can we mitigate this cost with caching?

Scenario 1 - The result of the calculation is of different sizes and dimensions each time, currently represented as a Perl data structure. Current solution, serialize it and store that string in the DB. Can it be done any other, better way?

Scenario 2 - The results of the calculations are the exact same size each time, maybe a different number of rows, but still the same columns no matter how you slice it. Can we just dump this data into a table and then make the PK somehow related to the inputs that the customer put in for the calculation? If already calculated, read cache, if not, calculate + store?

# ? May 6, 2008 17:41

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

var1ety posted:

Space is effectively unlimited on databases, but CPU power is not. You should design your database to answer the questions you plan to ask quickly and efficiently.

People model their database like this for a lot of reasons, but it's usually to avoid making concrete design decisions or to get around their DBAs (if it's for space it's an even worse reason).

It's no question that flattening the data to answer simple questions will be faster when that data is pre-flattened. You'll experience decreased concurrency through this system, and complex questions will be even harder for the database to answer quickly. God help you if you're using self joins to answer these questions.

Oh, I mistook your post for saying not to do the extra related table like I was suggesting. I agree 100% about the attributes thing.

# ? May 6, 2008 18:00

TiMBuS: Sep 25, 2007; LOL WUT?

chocojosh posted:

I don't think it's a problem to have 1000s of tables if your problem domain is so big that you'll actually need one thousand. I know at my company our web application (I'd estimate 0.5 M - 1 M LOC) has a few hundred database tables, and it's never been a problem.

Yeah but this is on the fly stuff. There could easily be thousands of tables.

yaoi prophet posted:

The correct way is to put all of your posts in one table. Creating individual tables would just be the wrong way to do it. Filtering is simply joining or adding a WHERE clause, and is the normal way of doing things.

Generally, creating tables on the fly is to be avoided in pretty much any database system.

Ahh god dammit. That's the answer I didn't want

Ah, well. Flat storage it is.

Thanks.

# ? May 7, 2008 06:12

Randomosity: Sep 21, 2003; My stalker WAS watching me...

Alright, I'm scratching my head on this, hopefully someone can help me out.

I've got 2 tables (event and user_event). Event is a small table that list the different actions users can make in our system. User_event is a log of those actions.

I want to get a count of how many times each action occurs based on the user id. Currently, I've got something like "SELECT count(*), event.type from user_event join event on event.id = user_event.id group by event.id". I say 'like' since the actual query is constructed by ZendFramework stuff.

this approach doesn't given me events for which there have been 0 actions, and I'm not sure how to do that. User_events is a massive table, so I really only want to query it once (otherwise I would just run a query for each event type.)

Is there a way I can include events all events (entries in user_events or not) with either a count of 0 or null, and still only hit user_event once? I tried a right join, didn't work.

# ? May 7, 2008 16:07

Rebus: Jan 18, 2006; Meanwhile, somewhere in Grove, work begins on next season's Williams F1 car...

I have a question I've been scratching my head over from a past exam paper from a course I'm currently sitting.

I have a table called HOTEL, with fields hotel_no, name, city, and telephone (but this is mostly irrelevant). I need to create a command to "Give a count of the hotels in each city, only showing the results for cities where there are more than three hotels".

I keep getting 'aggregate' errors when trying commands, though I know I have to use DISTINCT(city) at some point.

Any help?

Edit: It must be all in one command line, so just one query.

# ? May 7, 2008 16:54

Randomosity: Sep 21, 2003; My stalker WAS watching me...

I'm no expert, but would this work? I'm not sure about the 'where' clause.

SELECT count(*) AS number FROM hotel WHERE number >= 3 GROUP BY city

# ? May 7, 2008 17:09

Rebus: Jan 18, 2006; Meanwhile, somewhere in Grove, work begins on next season's Williams F1 car...

=> SELECT count(*) AS number FROM hotel WHERE number >= 3 GROUP BY city;
ERROR: Attribute "number" not found

Edit:

Ahhh thanks!!! This fixed it:

SELECT city, count(*) AS number FROM hotel group by city;

Rebus fucked around with this message at 17:21 on May 7, 2008

# ? May 7, 2008 17:14

Randomosity: Sep 21, 2003; My stalker WAS watching me...

Hmm, that's what I figured. Sorry.

# ? May 7, 2008 17:16

Code Jockey: Jan 24, 2006; 69420 basic bytes free

Rebus posted:

I have a question I've been scratching my head over from a past exam paper from a course I'm currently sitting.

I have a table called HOTEL, with fields hotel_no, name, city, and telephone (but this is mostly irrelevant). I need to create a command to "Give a count of the hotels in each city, only showing the results for cities where there are more than three hotels".

I keep getting 'aggregate' errors when trying commands, though I know I have to use DISTINCT(city) at some point.

Any help?

Edit: It must be all in one command line, so just one query.

I'd try this:

select city as 'City', count(hotel_no) as 'Count' from hotel group by city having count(hotel_no) >3

e. one line

Code Jockey fucked around with this message at 17:19 on May 7, 2008

# ? May 7, 2008 17:16

Code Jockey: Jan 24, 2006; 69420 basic bytes free

Rebus posted:

=> SELECT count(*) AS number FROM hotel WHERE number >= 3 GROUP BY city;
ERROR: Attribute "number" not found

Can't use aliases like that, just use count(*) again
[that works, right? I usually do a count on a specific column name]

e. actually that's wrong too, since the aggregate has to be used in a HAVING clause like my query does

# ? May 7, 2008 17:18

Rebus: Jan 18, 2006; Meanwhile, somewhere in Grove, work begins on next season's Williams F1 car...

Thanks that's sorted it

# ? May 7, 2008 17:23

rock2much: Feb 6, 2004; Grimey Drawer

This query works so far but the addition I'm trying to make explodes in my face:

code:

SELECT   DATE_KEY,
         SKU,
         WAREHOUSE_KEY,
         SUM(IIF(TYPE = 'I',QUANTITY,0)) AS QUANTITY_IN,
         SUM(IIF(TYPE = 'O',QUANTITY,0)) AS QUANTITY_OUT
FROM     DATEDIM,
         PRODUCT_TRANSACTION,
         WAREHOUSE
WHERE    DATEDIM.FULL_DATE = PRODUCT_TRANSACTION.DATE
         AND WAREHOUSE.WAREHOUSE_KEY = PRODUCT_TRANSACTION.WHSENUM
GROUP BY DATE_KEY,SKU,WAREHOUSE_KEY;

I'm trying to add 2 columns, 1 for "Quantity On Hand" which would be the difference between quantity_in and quantity_out. The second would be "Extended_price" which is quantity_on_hand multiplied by "Price" in the product_transaction table.

Is there anyway to bring these together in a single query?

# ? May 7, 2008 17:31

Aredna: Mar 17, 2007; Nap Ghost

Why not just calculate them as seperate columns?

code:

SUM(IIF(TYPE = 'I',QUANTITY,0)) - SUM(IIF(TYPE = 'O',QUANTITY,0)) AS QUANTITY_ON_HAND,
(SUM(IIF(TYPE = 'I',QUANTITY,0)) - SUM(IIF(TYPE = 'O',QUANTITY,0)) ) * PRODUCT_TRANSACTION.PRICE AS EXTENDED_PRICE

I'm not sure if this would be more or less efficient than doing it with a subquery.

# ? May 7, 2008 18:19

chocojosh: Jun 9, 2007; D00D.

Rebus posted:

=> SELECT count(*) AS number FROM hotel WHERE number >= 3 GROUP BY city;
ERROR: Attribute "number" not found

Edit:

Ahhh thanks!!! This fixed it:

SELECT city, count(*) AS number FROM hotel group by city;

You need to add the HAVING clause

I recommend you read over the W3C SQL tutorial: http://www.w3schools.com/sql/default.asp

It's very brief and won't give much detail, but it's perfect as a short introduction or to point you in the right direction if you have an idea of what you want.

# ? May 7, 2008 18:31

Adbot: ADBOT LOVES YOU

# ? Apr 29, 2024 17:29

rock2much: Feb 6, 2004; Grimey Drawer

Aredna posted:

Why not just calculate them as seperate columns?
code:
SUM(IIF(TYPE = 'I',QUANTITY,0)) - SUM(IIF(TYPE = 'O',QUANTITY,0)) AS QUANTITY_ON_HAND,
(SUM(IIF(TYPE = 'I',QUANTITY,0)) - SUM(IIF(TYPE = 'O',QUANTITY,0)) ) * PRODUCT_TRANSACTION.PRICE AS EXTENDED_PRICE
I'm not sure if this would be more or less efficient than doing it with a subquery.

Thanks that's what I ended up doing after consulting a classmate.

# ? May 7, 2008 21:03

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

«‹›303 »