January 6, 2017 at 1:27 pm
I have to write a query that lists orders and partitions them by customer id# and displays the date, customerid, name, order_amount and count on each line. I then need to match that against a retailer table to get the retailer id to display as well. The problem is it is a many to many relationship because the retailer ID is on the individual, not the store and the individual can have more than one store.
Here is my query:
WITH cte
AS
(
SELECT tbl1.Date, tbl1.ID, tbl1.Order_Amount, tbl2.StoreNum, COUNT(tbl1.id) over(partition by tbl1.id) as count
FROM tbl1 right join tbl2
ON tbl1.ID = tbl2.ID
)
SELECT * FROM cte
WHERE count > 10
So as I stated above the store owner has a single ID but can have multiple rows with different store numbers. So the owner with multiple stores has each order listed twice each Store number, which of course also throws off my counts and my WHERE clause.
I tried creating a variable outside of the CTE that lists the store numbers together and then use that in place of my tbl2.StoreNum column but that gave me an error:
No column name was specified for column 5 of 'CTE'.
Any suggestions are appreciated.
January 6, 2017 at 1:38 pm
I'm assuming that the query you provided is not your query which isn't running, as that's fine.
I don't think that using a variable is really the answer you need. Are orders not given to a store as well, then you can preserve the relationships.
The error tells you your problem though, Column 5 doesn't have a name. Give it a name (I can't fix your SQL as you gave us your working SQL without your variable).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 6, 2017 at 1:42 pm
WITH cte
AS
(
SELECT tbl1.Date, tbl1.ID, tbl1.Order_Amount, tbl2.StoreNum, ct = COUNT(tbl1.id) over(partition by tbl1.id)
FROM tbl1 right join tbl2
ON tbl1.ID = tbl2.ID
)
SELECT * FROM cte
WHERE ct > 10
I suspect the problem was that you tried to give the column a name which SQL Server recognises as a reserved word.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 6, 2017 at 2:47 pm
Phil Parkin (1/6/2017)
WITH cteAS
(
SELECT tbl1.Date, tbl1.ID, tbl1.Order_Amount, tbl2.StoreNum, ct = COUNT(tbl1.id) over(partition by tbl1.id)
FROM tbl1 right join tbl2
ON tbl1.ID = tbl2.ID
)
SELECT * FROM cte
WHERE ct > 10
I suspect the problem was that you tried to give the column a name which SQL Server recognises as a reserved word.
I did wonder that, but on SQL 2016 and .vNext it's more than happy to accept the column count.
I wonder, OP, did you perhaps have column 5 simplied declared as @Variable, rather than @Variable AS [count]?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 7, 2017 at 6:37 pm
I have to write a query that lists orders and partitions them by customer id# and displays the something_date, customer_id, generic_name, order_amount and something_count on each line (sic).
We need a little more information in this. Did you read the part of the form rules about posting DDL instead of a narrative? You also do not seem to know that "date" is the name of a data type in SQL, that there is no such thing as a generic name (has to be the name of something in particular), and likewise there is no generic count (has to be the count of something in particular) or generic id. Also, a line is a physical concept is a presentation layer and has nothing to do with the database. I think you might want a row, which is totally different.
I then need to match that against a retailers table to get the retailer id to display as well. The problem is it is a many to many relationship because the retailer ID is on the individual, not the store and the individual can have more than one store.
Now we have to start making guesses about keys, constraints, relationships and datatypes.
If you want to Google around for it, Phil Factor did some articles and humor pieces on "Tibbling"; the practice of using "tbl_" in table names. It shows that you are putting metadata into the database layer, which is a huge design error (it would really bore yourself with why this does not work read ISO 11179).
While the right outer join is not wrong (buy me a beer and I will tell you stories about how we got the outer joins into the ANSI standards), but it is usually used by people whose native language reads right to left. This is why you usually see left outer joins. Again, it is not wrong, but it is one of those little things that let you know something about the programmer and what is mindset is.
My guess right now is that this could be done with the GROUP BY clause of some kind. But without DDL. I have no idea.
Try again, and you and get a few hundred dollars worth of free advice on this forum. But you have to do your half of it 😉
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
January 8, 2017 at 10:49 am
CELKO (1/7/2017)
I have to write a query that lists orders and partitions them by customer id# and displays the something_date, customer_id, generic_name, order_amount and something_count on each line (sic).
We need a little more information in this. Did you read the part of the form rules about posting DDL instead of a narrative? You also do not seem to know that "date" is the name of a data type in SQL, that there is no such thing as a generic name (has to be the name of something in particular), and likewise there is no generic count (has to be the count of something in particular) or generic id. Also, a line is a physical concept is a presentation layer and has nothing to do with the database. I think you might want a row, which is totally different.
I then need to match that against a retailers table to get the retailer id to display as well. The problem is it is a many to many relationship because the retailer ID is on the individual, not the store and the individual can have more than one store.
Now we have to start making guesses about keys, constraints, relationships and datatypes.
If you want to Google around for it, Phil Factor did some articles and humor pieces on "Tibbling"; the practice of using "tbl_" in table names. It shows that you are putting metadata into the database layer, which is a huge design error (it would really bore yourself with why this does not work read ISO 11179).
While the right outer join is not wrong (buy me a beer and I will tell you stories about how we got the outer joins into the ANSI standards), but it is usually used by people whose native language reads right to left. This is why you usually see left outer joins. Again, it is not wrong, but it is one of those little things that let you know something about the programmer and what is mindset is.
My guess right now is that this could be done with the GROUP BY clause of some kind. But without DDL. I have no idea.
Try again, and you and get a few hundred dollars worth of free advice on this forum. But you have to do your half of it 😉
Just as we ask people to provide the DDL and readily consumable data, so do we ask responders to post useful answers where links are provided to support otherwise nearly useless anecdotal/rhetorical/opinionated recommendations.
With that in mind, please post a link to a free copy of the ISO 11179 specification/documentation. If no free copy is available, it's mostly a useless reference on your part and you should stop telling people to read it.
In the case of your post above, you should also provide a link to Phil Factor's article(s) on "tibbling". If it's something that you wish to site quite often, include it/them in your signature line so that you don't actually have to search for it nor remember to provide it every time you want to cite it. Here are a couple of the links you speak of.
http://www.sqlservercentral.com/articles/Editorial/69385/
https://www.simple-talk.com/sql/t-sql-programming/laying-out-sql-code/
As for how to post and setting the example, please see my signature line below. Look under "Helpful Links" and you'll find two links; one for how to post for code problems and one how to post for performance problems.
Last but not least, if you're going to insist on being a "regular", learn how to use the "IFCodes" on this forum to make your responses more useful, easier to read, and to provide for clickable links.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2017 at 4:51 pm
SELECT
tbl1.Date,
tbl1.ID,
tbl1.Order_Amount,
tbl2.StoreNum,
COUNT(tbl1.id) over(partition by tbl1.id) as count
FROM tbl1 right join tbl2
ON tbl1.ID = tbl2.ID
I always cringe a bit when I see those right joins.
Let us assume that you have one order at store A and none at store B. When joining tbl2 (left table here really) to tbl1 on ID, consider the following. If ID (say 100) has two stores/entries in tbl2 ((100, A),(100,B)) than that one order will create two joins. Can you see how?
Sorry if I am confused on what you are trying to accomplish exactly by having the join. Maybe post some sample data and the create table statements with what you want the final outcome to look like?
----------------------------------------------------
January 10, 2017 at 7:31 am
Wow - that was really stupid. It was the name of course.
January 11, 2017 at 8:32 am
scotdg (1/6/2017)
I have to write a query that lists orders and partitions them by customer id# and displays the date, customerid, name, order_amount and count on each line. I then need to match that against a retailer table to get the retailer id to display as well. The problem is it is a many to many relationship because the retailer ID is on the individual, not the store and the individual can have more than one store.Here is my query:
WITH cte
AS
(
SELECT tbl1.Date, tbl1.ID, tbl1.Order_Amount, tbl2.StoreNum, COUNT(tbl1.id) over(partition by tbl1.id) as count
FROM tbl1 right join tbl2
ON tbl1.ID = tbl2.ID
)
SELECT * FROM cte
WHERE count > 10
So as I stated above the store owner has a single ID but can have multiple rows with different store numbers. So the owner with multiple stores has each order listed twice each Store number, which of course also throws off my counts and my WHERE clause.
I tried creating a variable outside of the CTE that lists the store numbers together and then use that in place of my tbl2.StoreNum column but that gave me an error:
No column name was specified for column 5 of 'CTE'.
Any suggestions are appreciated.
Can you provide more detail on which table has which data? We need to understand the true nature of the relationship between the tables. If you have a table that has stores and orders and the owner data is in that table, then what is in the other table? Or, is it some other set up? We can't tell from your description exactly what the setup is. If you could post table create statements for these tables, along with INSERTs with sample data and the expected results if the sample data was your actual data, we could far more easily get to a good solution. We kind of have to be able to see what you are seeing in order to be on the same page. Please help us help you...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply