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 »

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

AskYourself posted:

You might be right

For a simple case I compared the execution plans of SELECT * vs SELECT 1 and they're identical.

# ? Nov 22, 2017 21:31

Adbot: ADBOT LOVES YOU

# ? Jun 4, 2024 22:52

FAT32 SHAMER: Aug 16, 2012

I never thought that I would miss SQL stuff until we started using Firebase

Don�t get me wrong, it can be nice, but it�s just so foreign compared to SQL

At least it does all the serialization of POJOs for me I guess

# ? Nov 27, 2017 21:14

Jose: Jul 24, 2007; Adrian Chiles is a broadcaster and writer

Is there an easy way to mass rename tables and columns without losing data?

# ? Nov 28, 2017 13:27

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Jose posted:

Is there an easy way to mass rename tables and columns without losing data?

I've never seen mass ways of doing this, but in SQL Server sp_rename is a thing. What is it you're really trying to do?

# ? Nov 28, 2017 14:17

mr_package: Jun 13, 2000

Jose posted:

What server role do I need in order to be able to use the SQL server import export wizard? I'm transferring tables across multiple databases/servers and I'm feeling lazy enough to not give myself data writer/reader on them all but don't want to leave the account with sysadmin

I have a similar but perhaps stupider question: what is the best way to export a table including all the mssql rules (must be unique, primary key, data types etc.) I have a test database and I want to make sure a table I create for an app update is working properly there before I add to the production database. But export options-- at least the human readable plain text ones-- seem to drop this information. There must be a very very simple and common way to do this, no?

# ? Nov 28, 2017 21:17

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

mr_package posted:

I have a similar but perhaps stupider question: what is the best way to export a table including all the mssql rules (must be unique, primary key, data types etc.) I have a test database and I want to make sure a table I create for an app update is working properly there before I add to the production database. But export options-- at least the human readable plain text ones-- seem to drop this information. There must be a very very simple and common way to do this, no?

To make sure I understand properly, by "export" you mean just the table structure and not the data contained within it, correct? If so, yeah that's super easy in the SSMS:

gives you

etc etc

# ? Nov 28, 2017 21:28

mr_package: Jun 13, 2000

YES that looks much much better; I was exporting to csv, txt, whatever human-readable export options I could find in SMSS and they were of course omitting all that stuff.

HA HA HA I'm in charge of the database that the entire company runs on.

# ? Nov 29, 2017 00:34

nwin: Feb 25, 2002; make's u think

Two very basic questions coming your way...

I have a Field in a table where the field is 'Firstname, Lastname', so it looks like:

'John, Smith'
'Jane, Doe'

How do I go about sorting this field by last name in alphabetical order, so it displays like:

'Doe, Jane'
'Smith, John'

Also, is there a way to add text into a field's data?

For example, I have a field that lists all of the hire dates for employees that displays as:
'11/11/1996'
'02/13/2008'

But I would like each individual entry to display as:

'Hired in 1996'
'Hired in 2008'

removing the mm/dd and only keeping the year. Any ideas?

# ? Nov 29, 2017 14:36

Shy: Mar 20, 2010

nwiniwn posted:

Two very basic questions coming your way...

I have a Field in a table where the field is 'Firstname, Lastname', so it looks like:

'John, Smith'
'Jane, Doe'

How do I go about sorting this field by last name in alphabetical order, so it displays like:

'Doe, Jane'
'Smith, John'

Also, is there a way to add text into a field's data?

For example, I have a field that lists all of the hire dates for employees that displays as:
'11/11/1996'
'02/13/2008'

But I would like each individual entry to display as:

'Hired in 1996'
'Hired in 2008'

removing the mm/dd and only keeping the year. Any ideas?

In MySQL:

SQL code:

SELECT employee_name,
CONCAT('Hired in ', SUBSTRING_INDEX(employee_date, '/', -1)) AS hired_in
FROM employees ORDER BY SUBSTRING_INDEX(employee_name, ', ', -1) ASC;

assuming the date is a string. If not, YEAR(employee_date) instead of SUBSTRING_INDEX(employee_date, '/', -1)

Shy fucked around with this message at 16:07 on Nov 29, 2017

# ? Nov 29, 2017 14:55

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

nwiniwn posted:

Two very basic questions coming your way...

I have a Field in a table where the field is 'Firstname, Lastname', so it looks like:

'John, Smith'
'Jane, Doe'

How do I go about sorting this field by last name in alphabetical order, so it displays like:

'Doe, Jane'
'Smith, John'

Also, is there a way to add text into a field's data?

For example, I have a field that lists all of the hire dates for employees that displays as:
'11/11/1996'
'02/13/2008'

But I would like each individual entry to display as:

'Hired in 1996'
'Hired in 2008'

removing the mm/dd and only keeping the year. Any ideas?

LastName, FirstName in MSSQL:

code:

select 
	substring(Name,patindex('%, %',Name)+2,len(Name)-(patindex('%, %',Name))+1)
	+ ', ' +
	substring(Name,0,patindex('%, %',Name))
	as formattedName
	from @myTable
	order by formattedName

Hired in Year in MSSQL:

code:

select 'Hired in ' + cast(year(HireDate) as nvarchar(4))
from @myTable

# ? Nov 29, 2017 15:07

nwin: Feb 25, 2002; make's u think

kumba posted:

LastName, FirstName in MSSQL:

code:

select 
	substring(Name,patindex('%, %',Name)+2,len(Name)-(patindex('%, %',Name))+1)
	+ ', ' +
	substring(Name,0,patindex('%, %',Name))
	as formattedName
	from @myTable
	order by formattedName

Hired in Year in MSSQL:

code:

select 'Hired in ' + cast(year(HireDate) as nvarchar(4))
from @myTable

Thanks for the help-I'm using Oracle. That function for hired in year says it's missing a right parenthesis.

# ? Nov 29, 2017 15:34

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

nwiniwn posted:

Thanks for the help-I'm using Oracle. That function for hired in year says it's missing a right parenthesis.

Oracle SQL apparently uses double bar instead of + for string concatenation, so try this:

code:

select 'Hired in' || cast(year(HireDate) as nvarchar(4))
from @myTable;

# ? Nov 29, 2017 15:42

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

nwiniwn posted:

Two very basic questions coming your way...

I have a Field in a table where the field is 'Firstname, Lastname', so it looks like:

'John, Smith'
'Jane, Doe'

How do I go about sorting this field by last name in alphabetical order, so it displays like:

'Doe, Jane'
'Smith, John'

Also, is there a way to add text into a field's data?

For example, I have a field that lists all of the hire dates for employees that displays as:
'11/11/1996'
'02/13/2008'

But I would like each individual entry to display as:

'Hired in 1996'
'Hired in 2008'

removing the mm/dd and only keeping the year. Any ideas?

Thanks for the help-I'm using Oracle. That function for hired in year says it's missing a right parenthesis.

code:

select regexp_replace(name,'^([^,]+),\s+(.*)$','\2, \1') as formatted_name
from table
order by formatted_name

code:

select 'Hired in ' ||to_char(hiredate,'YYYY') hired
from table

# ? Nov 29, 2017 15:44

nwin: Feb 25, 2002; make's u think

Jethro posted:

code:

select regexp_replace(name,'^([^,]+),\s+(.*)$','\2, \1') as formatted_name
from table
order by formatted_name

code:

select 'Hired in ' ||to_char(hiredate,'YYYY') hired
from table

This worked-thanks!

edit: however, the first code is only returning the info in the same format it began 'Firstname, Lastname', not 'Lastname, Firstname' like I was trying for:

code:

select regexp_replace(employee_name,'^([^,]+),\s+(.*)$','\2, \1') as formatted_name
from employees
order by formatted_name

nwin fucked around with this message at 16:06 on Nov 29, 2017

# ? Nov 29, 2017 15:56

the talent deficit: Dec 20, 2003; self-deprecation is a very british trait, and problems can arise when the british attempt to do so with a foreign culture

help me normalize this mess (postgres 9.6, but could pretty easily be 10 if it matters)

code:

CREATE TABLE team (
  id uuid PRIMARY KEY,
  captain uuid REFERENCES player(id) NOT NULL
);

CREATE TABLE player (
  id uuid PRIMARY KEY
);

CREATE TABLE team_member (
  team uuid REFERENCES team(id) NOT NULL,
  player uuid REFERENCES player(id) NOT NULL,
  captain boolean NOT NULL DEFAULT 'false'
);

CREATE UNIQUE INDEX single_captain_idx ON team_member (team) WHERE captain = 'true';
CREATE UNIQUE INDEX single_place_on_team_idx ON team_member(team, player);

the goal here is to make it so a team can have only a single captain and any number of team members, but the captain can't appear as a player. i'm trying to eliminate the `captain` field in `team_member` or the `captain` field in `team` but if i remove it from `team` i can construct a team with no captain and if i remove it from `team_member` i can have a team where a player is both captain and just a regular player. i can't put it on `player` because a player can simultaneously be on multiple teams sometimes as captain sometimes as not

totally ok with exclude or check constraints, altho i'm not sure they work here

edit: i solved this, check it:

code:

CREATE TABLE team (
  id uuid PRIMARY KEY,
  captain uuid
);

CREATE TABLE player (
  id uuid PRIMARY KEY
);

CREATE TABLE team_member (
  team uuid REFERENCES team(id) NOT NULL,
  player uuid REFERENCES player(id) NOT NULL
);

CREATE UNIQUE INDEX single_place_on_team_idx ON team_member(team, player);

ALTER TABLE team ADD CONSTRAINT check_this_shit_out FOREIGN KEY (id, captain)
  REFERENCES team_member (team, player)
  DEFERRABLE INITIALLY DEFERRED;

the talent deficit fucked around with this message at 04:49 on Nov 30, 2017

# ? Nov 30, 2017 01:55

Busy Bee: Jul 13, 2004

edit

Busy Bee fucked around with this message at 14:24 on Dec 1, 2017

# ? Nov 30, 2017 04:46

skooma512: Feb 8, 2012; You couldn't grok my race car, but you dug the roadside blur.

I'm also boning up on SQL in preperation for an interview. They sent me a packet of questions that I totally loving bombed but for some reason they still want to talk to me.

One question was take a select query and spot the problem. I stated what the query was supposed to do (get a list of people who did not win a game), but not what was wrong. I threw it up in MS Access to see what happened and it returns nothing unless I take the subquery SELECT heat_id FROM players and add WHERE IS NOT NULL to the end, which causes it to suddenly put out the data I wanted. There is a game where the heat_id is null.

What gives? I would figure the subquery would just pull what it can and ignore the null, it certainly does when you isolate it. Put it in a subquery and all of a sudden the null brings the whole thing to a screeching halt.

The full query was

SELECT id from players
WHERE id not in (select heat_id from players)

This doesn't return anything unless I stick where is not null to the end of the subquery.

# ? Nov 30, 2017 07:55

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

skooma512 posted:

I'm also boning up on SQL in preperation for an interview. They sent me a packet of questions that I totally loving bombed but for some reason they still want to talk to me.

One question was take a select query and spot the problem. I stated what the query was supposed to do (get a list of people who did not win a game), but not what was wrong. I threw it up in MS Access to see what happened and it returns nothing unless I take the subquery SELECT heat_id FROM players and add WHERE IS NOT NULL to the end, which causes it to suddenly put out the data I wanted. There is a game where the heat_id is null.

What gives? I would figure the subquery would just pull what it can and ignore the null, it certainly does when you isolate it. Put it in a subquery and all of a sudden the null brings the whole thing to a screeching halt.

The full query was

SELECT id from players
WHERE id not in (select heat_id from players)

This doesn't return anything unless I stick where is not null to the end of the subquery.

x NOT IN (NULL) evaluates to UNKNOWN per standard SQL.
x NOT IN (x, NULL) evaluates to FALSE per standard SQL.
All possible values of x give a result of either UNKNOWN or FALSE if there are NULLs in the IN-list, so you'll never select anything.

# ? Nov 30, 2017 11:18

nwin: Feb 25, 2002; make's u think

Alright, new question...in my table "employees", I have names given as:

employee_name = 'Firstname, Lastname'

ex: John, Smith
Jackie, Doe
Morgan, James

I want to get a count of each person's first letter of their last name, so if I had 3 people whose last name started with S, 4 started with T, and 2 started with A, it would return this:

A: 2
S: 4
T: 3

This is the code I have so far...but it's returning everything by the first letter of the first name...since people have different amounts of characters in their first name, I'm not sure how to move it so it just shifts to the second word in the field, after the comma...any ideas?

code:

 select substr(employee_name,1,1) as Last_name, count(student_name) 
  from employee
 group by substr(employee_name,1,1)
order by 1

# ? Nov 30, 2017 14:21

Sir Bobert Fishbone: Jan 16, 2006; Beebort

Take a look at CHARINDEX to hone in on the comma.

# ? Nov 30, 2017 15:27

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

nwiniwn posted:

Alright, new question...in my table "employees", I have names given as:

employee_name = 'Firstname, Lastname'

This makes me sad. Send a sternly worded letter demanding that they stop breaking zeroeth normal form with this poo poo. One piece of data per column dammit.

# ? Nov 30, 2017 16:06

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

his questions read like homework problems so i'm assuming these are not real world examples because holy gently caress if that's a real thing whoever designed it should be shot

# ? Nov 30, 2017 16:10

nwin: Feb 25, 2002; make's u think

Yeah it�s for a 6-week MBA course on Databases, where the first two were spent on Access and the last 4 is SQL. No guidance was given on this so I�m trying to google whatever I can to figure things out.

edit: I think I've got it so it will sort by the second word...would this be correct?

code:

select substr(employee_name,1,+1) as Last_name, count(employee_name) 
  from employees
 group by substr(employee_name,1,+1)
order by 1

nwin fucked around with this message at 16:49 on Nov 30, 2017

# ? Nov 30, 2017 16:39

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

but hopefully they are using this as a "teachable moment" kind of thing and saying "this is the kind of problem you get if you don't design your database properly"?

# ? Nov 30, 2017 17:38

nwin: Feb 25, 2002; make's u think

Hammerite posted:

but hopefully they are using this as a "teachable moment" kind of thing and saying "this is the kind of problem you get if you don't design your database properly"?

haha. no...at least not yet.

# ? Nov 30, 2017 17:41

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

Solve every problem by adding two computed columns 'firstname' and 'lastname' and then doing proper joins over those.

Or for more passive-aggressiveness, copy the data over to a normalized table, DROP the old table, then proceed to solve the problem.

# ? Nov 30, 2017 18:19

skooma512: Feb 8, 2012; You couldn't grok my race car, but you dug the roadside blur.

Hammerite posted:

x NOT IN (NULL) evaluates to UNKNOWN per standard SQL.
x NOT IN (x, NULL) evaluates to FALSE per standard SQL.
All possible values of x give a result of either UNKNOWN or FALSE if there are NULLs in the IN-list, so you'll never select anything.

Thanks! That makes sense.

# ? Nov 30, 2017 19:07

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

NihilCredo posted:

Solve every problem by adding two computed columns 'firstname' and 'lastname' and then doing proper joins over those.

Or for more passive-aggressiveness, copy the data over to a normalized table, DROP the old table, then proceed to solve the problem.

It's so beautiful. I love this.

# ? Nov 30, 2017 21:30

Kwilty: May 31, 2011

I currently have a problem where I am trying to multiply an integer by the number of non-null values across a few other columns. However, the columns being checked hold letters rather than integers.

In this example I'd basically want my query to return a column that would say

220
220
110
220
160

# ? Dec 1, 2017 20:50

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Kwilty posted:

I currently have a problem where I am trying to multiply an integer by the number of non-null values across a few other columns. However, the columns being checked hold letters rather than integers.

In this example I'd basically want my query to return a column that would say

220
220
110
220
160

code:

select 
	min_per_meeting * len(
			coalesce(monday_cde,'')
			+coalesce(tuesday_cde,'')
			+coalesce(wednesday_cde,'')
			+coalesce(thursday_cde,'')
			+coalesce(friday_cde,'')
			)
		as Multiplier
	from @myTable

# ? Dec 1, 2017 21:02

Kwilty: May 31, 2011

kumba posted:

code:

select 
	min_per_meeting * len(
			coalesce(monday_cde,'')
			+coalesce(tuesday_cde,'')
			+coalesce(wednesday_cde,'')
			+coalesce(thursday_cde,'')
			+coalesce(friday_cde,'')
			)
		as Multiplier
	from @myTable

holy poo poo that there is some good thinkin.

e: I told myself I was overthinking this too.

# ? Dec 1, 2017 21:19

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

sometimes all it needs is a second pair of eyes

# ? Dec 1, 2017 21:26

Kwilty: May 31, 2011

kumba posted:

code:

select 
	min_per_meeting * len(
			coalesce(monday_cde,'')
			+coalesce(tuesday_cde,'')
			+coalesce(wednesday_cde,'')
			+coalesce(thursday_cde,'')
			+coalesce(friday_cde,'')
			)
		as Multiplier
	from @myTable

So sadly the database we use for some reason has a mix of blanks and null values for these fields, and it appears to be messing with the calculation.

# ? Dec 1, 2017 23:07

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Kwilty posted:

So sadly the database we use for some reason has a mix of blanks and null values for these fields, and it appears to be messing with the calculation.

well that's stupid as hell but should be easy enough to fix

code:

select 
	min_per_meeting * len(
			ltrim(rtrim(coalesce(monday_cde,'')))
			+ltrim(rtrim(coalesce(tuesday_cde,'')))
			+ltrim(rtrim(coalesce(wednesday_cde,'')))
			+ltrim(rtrim(coalesce(thursday_cde,'')))
			+ltrim(rtrim(coalesce(friday_cde,'')))
			)
		as Multiplier
	from @myTable

you can actually do it without both trims but just in case somehow one of those entries ends up as something like 'M ' or ' M' instead of 'M' having both trims will handle that case as well

# ? Dec 1, 2017 23:14

Busy Bee: Jul 13, 2004

Basic GROUP BY question. Why is it that when there are multiple columns involved that you have to include both columns when doing the GROUP BY statement. For example http://www.dofactory.com/sql/having

SELECT AVG(TotalAmount), FirstName, LastName
FROM [Order] O JOIN Customer C ON O.CustomerId = C.Id
GROUP BY FirstName, LastName
HAVING AVG(TotalAmount) BETWEEN 1000 AND 1200

Why can't I just say "GROUP BY FirstName"? If I do that it brings an error and I have to include LastName as well. What is the reasoning behind that?

# ? Dec 2, 2017 07:22

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

"When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column."

... group by firstname,lastname; distinguishes between "John Doe" and "John Deer".

select sum(amount),firstname group by firstname; accumulates their amounts into the same row.

(If you select sum(a),b,c group by b; it's like saying you want a join on field c, but you haven't specified how that join is to occur, so it gets confused.)

# ? Dec 2, 2017 07:45

Gatac: Apr 22, 2008; Fifty Cent's next biopic.

Because you're telling the DBMS which information from individual rows is relevant and which is to be summarized by the aggregate function. Try to forget the "simple" idea that the GROUP BY clause just automatically gets all the non-aggregate stuff from the SELECT clause, as if that was just syntax bookkeeping SQL makes you do for no good reason. Turn your thinking around instead: always start by considering what kinds of information you want to keep significant. Do you want an average just over the first name of your customers, like the average amount a "Jason" or "Melinda" has, or do you want the average for every unique combination of first and last name, as in separate values for a "Jason Alberich" vs a "Jason Smith" (but still aggregating over every "Jason Smith", if there are multiples)? This will determine your GROUP BY clause, and once you have your GROUP BY clause figured out, then you know what is available to you in the SELECT clause.

Remember that GROUP BY aggregates data. You can't SELECT columns that aren't in the clause because those columns have been discarded in the process of producing those aggregate values. There are no individual rows of the base table "hidden" here, because you're not dealing with the base table, you're dealing with the aggregation you ordered the DBMS to create from it. The columns in the GROUP BY clause are all that's left because that's what you told the DBMS is significant, everything else got folded up.

If you're looking for a solution that lets you work with individual rows and compute aggregate values to just appear as another column - like, say, you want the full record for Joey Dudeguy from Squirreltown, but want a pseudo-column tacked on that contains the average spending for all people from Squirreltown - you're going to want to look into analytic (or "window") functions.

# ? Dec 2, 2017 07:48

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

For me, one of the most important steps to truly grokking SQL (and it's a language that flies once you grok it) was learning the order in which a query is executed.

This is for SQL Server but I think most databases will be the same:

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

Memorize that list (well, maybe not the WITH CUBE / ROLLUP, whatever it is). Once you do that, a lot of questions and 'why doesn't this query work??' moments suddenly evaporate. It's obvious why you can use your custom named columns in some parts of the query but not others. It's obvious what happens when you use DISTINCT(SUM()) and GROUP BY in the same query. It's obvious why putting a condition in the JOIN or in the WHERE is equivalent. Et cetera.

# ? Dec 2, 2017 10:37

Busy Bee: Jul 13, 2004

Thank you for the clarification.

I moved onto the ANY and ALL operators and I'm having a hard time wrapping my head around it while I prepare for this analyst interview. Here is the resource I am using: https://www.w3schools.com/sql/sql_any_all.asp

I don't understand the use of the ALL operator, especially with a data set. I would assume its not that common for a column of data to ALL be the same where it returns TRUE?

None of the videos on YouTube all the tutorials really explains it well in using ANY / ALL operators.

In addition, why would someone want to use a subquery rather than using JOIN. Are they not similar?

Busy Bee fucked around with this message at 03:44 on Dec 3, 2017

# ? Dec 3, 2017 03:21

Adbot: ADBOT LOVES YOU

# ? Jun 4, 2024 22:52

Gatac: Apr 22, 2008; Fifty Cent's next biopic.

That depends on how you define the condition. Example:

code:

SELECT ProductName
  FROM Products
 WHERE ProductID = ALL (SELECT ProductID FROM Orders WHERE Status = 'Shipped')

A listing of all products for which there are only orders that have already shipped. If there is one order in any other status than "Shipped", that product won't be returned. If you're thinking "I could do that with a JOIN", you're not wrong. There's usually more than one way to skin a cat and which one you use depends on the specific use case, what your DBMS supports and your personal preference.

Similarly, subqueries aren't always the solution, but they have their uses. Example:

code:

SELECT ProductName,
       ProductID,
       ProductInventor,
       ProductDateOfInvention,
       (SELECT count(1)
          FROM Orders
         WHERE Orders.ProductID = Products.ProductID) as OrderCount
  FROM Products

You could join the Orders table and aggregate over the whole thing, but if you want multiple columns from the Products table, your GROUP BY statement could quickly swell. By doing it this way and using a subquery to only look at Orders for a specific ProductID, you save yourself that hassle - in fact, you don't even need a GROUP By statement this way because you only need a straight count of all the rows in that subquery. Note also how the subquery allows you to reference a column from the main query inside it. If you were to do this by JOINing, it'd look like this instead:

code:

SELECT ProductName,
       ProductID,
       ProductInventor,
       ProductDateOfInvention,
       X.OrderCount
  FROM Products
  JOIN (SELECT count(1) as OrderCount,
	       ProductID,
          FROM Orders
	 GROUP BY ProductID) X
    ON Products.ProductID = X.ProductID

Your call as to which is more comprehensible to you.

# ? Dec 3, 2017 07:33

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

«‹›303 »