I have a simple test table, i want to add a column at the end that counts all of the ParentIntPart that are the same,
for ParentIntPart = 3 the column would show and, 4 it will show 4 and 5 will be 1
any ideas?
Thank you for your quick reply, adding rollup to my query doesn't shows the results i'm after,
added it to excel to hopefully explain better
the Count Parentintpart is just grouping into one row
ok let me try it for you !!!!
once i get the results i will get back !!!
1 Like
hi
i have first created the SAMPLE data script ..
so that anyone else also can try
please click arrow to the left for drop create sample data SCRIPT
drop table #sampledata
go
create table #sampledata
(
ParentUsage varchar(50),
ParentIntPart int ,
ParentPartNo varchar(50),
ParentSuffix varchar(1),
ParentPartDesc varchar(100),
ParentMajRev varchar(1),
ParentMinRev int
)
go
insert into #sampledata select
'120USE26370',3,'A000Z1029',
'V','GREASE-LMX RUBBER (CASTROL)',
'A',1
insert into #sampledata select
'138USE22110',3,'A000Z1029',
'V','GREASE-LMX RUBBER (CASTROL)',
'A',1
insert into #sampledata select
'138USE22089',3,'A000Z1029',
'V','GREASE-LMX RUBBER (CASTROL)',
'A',1
insert into #sampledata select
'120USE32028',3,'A000Z1029',
'V','GREASE-LMX RUBBER (CASTROL)',
'A',1
insert into #sampledata select
'120USE32029',3,'A000Z1029',
'V','GREASE-LMX RUBBER (CASTROL)',
'A',1
insert into #sampledata select
'138USE27451',3,'A000Z1029',
'V','GREASE-LMX RUBBER (CASTROL)',
'A',1
insert into #sampledata select
'138USE27452',3,'A000Z1029',
'V','GREASE-LMX RUBBER (CASTROL)',
'A',1
insert into #sampledata select
'120USE26154',3,'A000Z1029',
'V','GREASE-LMX RUBBER (CASTROL)',
'A',1
-------------------------------------
insert into #sampledata select
'145USE03431',4,'A000Z1054',
'V','GREASE-RENOLIT LX-EP@ 12.5 KG',
'A',1
insert into #sampledata select
'132USE25278',4,'A000Z1054',
'V','GREASE-RENOLIT LX-EP@ 12.5 KG',
'A',1
insert into #sampledata select
'138USE18168',4,'A000Z1054',
'V','GREASE-RENOLIT LX-EP@ 12.5 KG',
'A',1
insert into #sampledata select
'138USE23864',4,'A000Z1054',
'V','GREASE-RENOLIT LX-EP@ 12.5 KG',
'A',1
-------------------------------------
insert into #sampledata select
'132USE14338',5,'A000Z1063',
'V','GREASE-SILKEASE COPPER 500G TIN',
'A',1
go
select * from #sampledata
go
1 Like
hi i got the result
but by tweaking the rollup part .. with where clause
rollup may not be the best thing to use !!!
please click arrow to the left for ANOTHER WAY to do this
--------------------------------------------------------------------------------------------------
there is another way to do this
by doing joins
first
do this
select
ParentIntPart,
sum(parentMinRev)
from #sampledata
group by ParentIntPart
then join to original set with union all
select from #sampledata
union all
select ParentIntPart , null,null,null,null, sum(parentMinRev)
from #sampledata group by ParentIntPart
--------------------------------------------------------------------------------------------
please click arrow to the left for SQL of ROLLUP
; WITH cte
AS (SELECT parentintpart,
parentusage,
parentpartno,
parentsuffix,
parentpartdesc,
parentmajrev,
Sum(parentminrev) AS sumparminrev
FROM #sampledata
GROUP BY rollup( parentintpart, parentusage, parentpartno,
parentsuffix,
parentpartdesc, parentmajrev ))
SELECT *
FROM cte
WHERE parentmajrev IS NOT NULL
OR parentusage IS NULL
go
thank you again,
still looking into this, Rollup seems to be giving so strange results in the full query so wondering is anyone else has another way of doing this
Yes there is another way
I have explained it
In my reply
I'm not 100% sure if I understand what you want in the result set.
I assumed that you want to show
- one record per parentintpart
- with an extra column at the end that counts the number of records with the same parentintpart
In the example you provided, the "one record per parentintpart" is the one with the lowest ParentUsage number.
Give this query a try: (I shamelessly reused harishgg1's table with sample data).
Click for the SQL script
WITH Count_parentintparts
AS (-- count #records with the same parentintpart
SELECT parentintpart
,count(*) AS Count_parentintpart
FROM #sampledata
GROUP BY parentintpart
),
SD AS ( --make preparations to select the record with the lowest ParentUsage nbr.
SELECT ParentUsage
,ParentIntPart
,ParentPartNo
,ParentSuffix
,ParentPartDesc
,ParentMajRev
,ParentMinRev
, ROW_NUMBER() OVER (PARTITION BY ParentIntPart ORDER BY ParentUsage ASC) as RowNum
FROM #sampledata
)
SELECT SD.*
,CPIP.Count_parentintpart
FROM SD
INNER JOIN Count_parentintparts as CPIP
ON SD.ParentIntPart = CPIP.ParentIntPart
WHERE SD.RowNum = 1 -- of all the records, select the one with the lowest ParentUsage nbr
1 Like
Hi Alan
I know of another way
It's my bedtime
I will do it tomorrow
For sure
I know how to do it
Just a matter of doing it
Involves
Union all
And sum of
Ooops
Looks like wim leys has already done it
Thank you for letting me use your table with sample data. 
It's really cool to see how you tackle a problem: with a temporary table, sample data, ... real nice!
I've not been on this forum for a long time so I haven't figured out yet how you hide your SQL code under a " please click arrow to the left for XXXXXX". But it has intrigued me from the first time I noticed it.
So, how do you do it?
Kind regards
William
Hi
Its my bedtime
I will show you tomorrow
Sooo easy! Very clear. Thanks a lot!
Hi Alan
I have come up with the solution using Wim Leys ... sql
please see if it helps !!!!
i used 100 as rownum field ..that should be fixed
please click arrow to the left for SQL
;WITH count_parentintparts
AS (-- count #records with the same parentintpart
SELECT parentintpart,
Count(*) AS Count_parentintpart
FROM #sampledata
GROUP BY parentintpart),
sd
AS (
--make preparations to select the record with the lowest ParentUsage nbr.
SELECT parentusage,
parentintpart,
parentpartno,
parentsuffix,
parentpartdesc,
parentmajrev,
parentminrev,
Row_number()
OVER (
partition BY parentintpart
ORDER BY parentusage ASC) AS RowNum
FROM #sampledata)
SELECT *
FROM (SELECT parentintpart,
parentusage,
parentpartno,
parentsuffix,
parentpartdesc,
parentmajrev,
parentminrev,
rownum
FROM sd
UNION ALL
SELECT parentintpart,
NULL,
NULL,
NULL,
NULL,
NULL,
count_parentintpart,
100
FROM count_parentintparts) a
ORDER BY a.parentintpart,
a.rownum
go
Thank you both for all your help