SQLTeam.com | Weblogs | Forums

SQL Count column

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?

hi

please see below picture
this should help !!!

1 Like

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 !!!

:slight_smile:

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

harishgg1

Thank you for letting me use your table with sample data. :wink:

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

:+1::+1:

please see this image if it helps you understand what to do !!!

if not .. please let me know

1 Like

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