March 28, 2016 at 5:03 pm
Hello Awesome Help Forum,
I am new to SQL server and I have a question on the logic of this code:
SELECT customers.cust_name,
custimers.cust_id
Count(order.order_num) as num_ord
FROM customers INNER JOIN orders
ON customers.cust_id=order.cust_id
GROUP BY customers.cust_name,
customers.cust_id;
March 28, 2016 at 5:18 pm
So what was your question?
From the looks of it, you have a typo... or several. You can use joins, but your SQL has some typos.
customers <> custimers
orders <> order
SELECT customers.cust_name,
custimers.cust_id
Count(order.order_num) as num_ord
FROM customers INNER JOIN orders
ON customers.cust_id=order.cust_id
GROUP BY customers.cust_name,
customers.cust_id;
you might want to use aliases to make your code easier to write...
SELECT c.cust_name,
c.cust_id,
Count(o.order_num) as num_ord
FROM customers AS c INNER JOIN orders AS o
ON c.cust_id=o.cust_id
GROUP BY c.cust_name,
c.cust_id;
March 28, 2016 at 5:35 pm
Sorry the my example and code did not post correctly due to me. Here is my example and 2 questions:
SELECT customers.cust_name,
customers.cust_id,
Count(orders.order_num) as num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = order.cust_id
Group BY customers.cust_name,
customers.cust_id;
My 2 questions are:
1) In the SELECT statement, how does the Count function know that there is a relationship between customers.cust_name and customers.cust_id when it counts the num_ord? 2) With the GROUP BY clause, how does it know that there is a relationship between the customers.cust_name and customers.cust_id? I am think that SQL server is running the GROUP BY individually for each of the columns. How does it keep the rows for each column related?
March 28, 2016 at 5:40 pm
SQL 2016 (3/28/2016)
Sorry the my example and code did not post correctly due to me. Here is my example and 2 questions:
SELECT customers.cust_name,customers.cust_id,
Count(orders.order_num) as num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = order.cust_id
Group BY customers.cust_name,
customers.cust_id;
My 2 questions are:
1) In the SELECT statement, how does the Count function know that there is a relationship between customers.cust_name and customers.cust_id when it counts the num_ord? 2) With the GROUP BY clause, how does it know that there is a relationship between the customers.cust_name and customers.cust_id? I am think that SQL server is running the GROUP BY individually for each of the columns. How does it keep the rows for each column related?
GROUP BY knows nothing about relationships. It merely aggregates rows, one for every discrete combination of (Cust_Name, Cust_Id) from your underlying data set.
The result of the COUNT() is the number of rows in the underlying data set for that particular combination of (Cust_Name, Cust_Id).
By 'underlying data set', I am referring to the results you would obtain from the query if the GROUP BY and COUNT() were omitted.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 28, 2016 at 8:28 pm
Thank you ...Tremendous help for me
March 29, 2016 at 7:57 am
T-SQL was designed to mirror English, so the order of processing does not match the written order. Queries are processed in the following order:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
TOP/OFFSET-FETCH
Each step produces a virtual table[1] that is then operated on by the following steps. Since the FROM clause is evaluated first, all subsequent steps have access to the results of that evaluation.
This is why you can only use column aliases in the ORDER BY and TOP/OFFSET-FETCH clauses. They're the only steps that have access to the results of the SELECT clause.
Drew
[1] Technically, the results of the last two steps are not tables, because they are ordered and tables are unordered.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply