Adding extra columns to count
-
Hey Experts,
I currently have a stored procedure that has a select statement that selects some columns, looks something like this
SELECT ProductDetail.Productid, product.classificationNo, product.name 'name', product.maker, product.yrmade,
(case
when yrmadeid IN (100,200,300) then 'latest version'
else 'Old version'
end) 'version'
FROM
ProductDetail
JOIN Product
ON Product.productid = productdetail.productid
This returns some results, looks something like this
ProductId |ClassificationNo | Name | Maker |YearMade | Version
___________________________________________________________________________
200 |10000 | Hitachi | Yokuha |1975 |old version
300 |10500 | Sony | SonyCorp|1985 |New Version
400 |10500 | Sony | SonyCorp|1985 |New Version
500 |10500 | Sony | SonyCorp|1985 |New Version
600 |10500 | Sony | SonyCorp|1985 |New Version
Here is the problem, I want to add 2 new columns to this select statement. The first one will be like a count to see how many products of the same type that there are e.g, there are 4 sony products so the 1st new column for sony should show '4'. Then the next column should actually count the products out starting with 1. E.g, the 2nd new column for sony should have 1, 2, 3, 4 to have something looking like this
ProductId |ClassificationNo | Name | Maker |YearMade | Version |1st new column |2nd new column
________________________________________________________________________________________________________
200 |10000 | Hitachi | Yokuha |1975 |old version |1 |1
300 |10500 | Sony | SonyCorp|1985 |New Version |4 |1
400 |10500 | Sony | SonyCorp|1985 |New Version |4 |2
500 |10500 | Sony | SonyCorp|1985 |New Version |4 |3
600 |10500 | Sony | SonyCorp|1985 |New Version |4 |4
How can I solve this please? Any help will do. Thanks
-
Could you provide the DDL for the table and put the sample data into a format that can be readily consummed to load the table? Please read the first article in my signature block for more info on how to to this.
-
Untested...
SELECT ProductDetail.Productid, product.classificationNo, product.name 'name', product.maker, product.yrmade,(case
when yrmadeid IN (100,200,300) then 'latest version'
else 'Old version'
end) 'version',
COUNT(*) OVER(PARTITION BY product.name),
ROW_NUMBER() OVER(PARTITION BY product.name ORDER BY ProductDetail.Productid)
FROM
ProductDetail
JOIN Product
ON Product.productid = productdetail.productid
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hey Lynn,
Nice article on forum etiquette, here is some ddl information, hopefully it helps clarify my question.
ddl for productdetail table
CREATE TABLE #ProductDetail
(
ProductID INT PRIMARY KEY CLUSTERED,
Price Money,
Quantity smallint,
CustomerID int
)
insert into #ProductDetail
values(200, 2000, 150, 101010)
insert into #ProductDetail
values(300, 2500, 150, 101020)
insert into #ProductDetail
values(400, 2500, 150, 101030)
insert into #ProductDetail
values(500, 2500, 150, 101030)
insert into #ProductDetail
values(600, 2500, 150, 101030)
ddl for product table
CREATE TABLE #Product
(
ProductID INT PRIMARY KEY CLUSTERED,
ClassificationNo char(5),
Name varchar(10),
Maker varchar(20),
)
insert into #Product
values(200, 10000, 'Hitachi', 'yokuha')
insert into #Product
values(300, 10500, 'sony', 'SonyCorp')
insert into #Product
values(400, 10500, 'sony', 'SonyCorp')
insert into #Product
values(500, 10500, 'sony', 'SonyCorp')
insert into #Product
values(600, 10500, 'sony', 'SonyCorp')
What I have,
select pd.ProductID, p.ClassificationNo, p.Name, p.maker
FROM #ProductDetail pd
join #Product p on pd.ProductID = p.ProductID
What I want to see
for every name that is present more than once e.g sony (4 times) and panasonic (2 times), I want 2 new columns that count the total number of names, and another one to number them. Below is the ddl and information of what I want to see
CREATE TABLE #WhatIWantToSee
(
ProductID INT PRIMARY KEY CLUSTERED,
ClassificationNo char(5),
Name varchar(10),
Maker varchar(20),
CountNamesOfTheSameProduct int,
NumberSameProductsASC int
)
insert into #WhatIWantToSee
values(200, 10000, 'Hitachi', 'yokuha', 1, 1)
insert into #WhatIWantToSee
values(300, 10500, 'sony', 'SonyCorp', 4, 1)
insert into #WhatIWantToSee
values(400, 10500, 'sony', 'SonyCorp', 4, 2)
insert into #WhatIWantToSee
values(500, 10500, 'sony', 'SonyCorp', 4,3)
insert into #WhatIWantToSee
values(600, 10500, 'sony', 'SonyCorp',4,4)
insert into #WhatIWantToSee
values(700, 10600, 'panasonic', 'panasonicCorp',2,1)
insert into #WhatIWantToSee
values(800, 10600, 'panasonic', 'panasonicCorp',2,2)
SELECT * FROM #WhatIWantToSee
should give something like this
ProductID ClassificationNo Name Maker CountNamesOfTheSameProduct NumberSameProductsASC
----------- ---------------- ---------- -------------------- -------------------------- ---------------------
200 10000 Hitachi yokuha 1 1
300 10500 sony SonyCorp 4 1
400 10500 sony SonyCorp 4 2
500 10500 sony SonyCorp 4 3
600 10500 sony SonyCorp 4 4
700 10600 panasonic panasonicCorp 2 1
800 10600 panasonic panasonicCorp 2 2
Mark, thanks for the code, it doesn't do exactly what I want though. The solution you gave counts all products and also numbers them. This could have been because I did not provide any ddl like lynn said. Hope the ddl information above explains the question better. Thanks guys!
-
I can't get the information for Panasonic from the data in your Product and ProductDetail tables, they don't exist. Also, Mark's code is very close to what you are looking for based on your latest post.
Here is the code I put together:
select
p.ProductID,
p.ClassificationNo,
p.Name,
p.Maker,
count(*) over (partition by p.ClassificationNo) as CountOfProducts,
row_number() over (partition by p.ClassificationNo order by p.ProductID asc) as ProductNo
from
#Product p
inner join #ProductDetail pd
on (p.ProductID = pd.ProductID);
-
Not only that, it looks like we could completely drop the #ProductDetail table, as it adds nothing to the query from what I can see.
-
Worked like a charm. Thank you guys very much. Ill take this as my introduction to the usage of the row_number function. Thanks again. Lynn, about the extra table, I didn't add all the columns so that was why it seemed like it wasn't needed. You guys have a great day!!
-
Glad it worked, and thanks for the feedback.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply