Retrive SUM for all the count that has same code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Retrive SUM for all the count that has same code

Author

Topic

ArunPhilip
Starting Member

21 Posts

Posted - 2012-09-18 : 14:58:07

Hi Everyone,

I am stuck and need help. I have written a query that returns the count of types based on code. In the result there are few records that have the same code but different types, for such records I need to add up the count and display it as another column. Any help in this matter is much appriciated.


SELECT 
      Count(a.TypeID)CountOfTypes,b.[TypeName], a.Code
  FROM [Sets] a
  LEFT JOIN [Types] b
    ON a.[TypeID] = b.[TypeID]
WHERE AppCode <> 'ABC' AND AssetAppCode <> 'DEF' AND
a.TypeID IN (54,57) AND a.LocationID=64 Group By a.Code,b.[TypeName]

I also tried to use a derived table, but didnt help.


SELECT SUM(X.CountOfTypes)as SUMPERAPP,X.[TypeName],X.CountOfTypes
FROM
(
SELECT 
      Count(a.TypeID)CountOfTypes,b.[TypeName], a.Code
  FROM [Sets] a
  LEFT JOIN [Types] b
    ON a.[TypeID] = b.[TypeID]
WHERE AppCode <> 'ABC' AND AssetAppCode <> 'DEF' AND
a.TypeID IN (54,57) AND a.LocationID=64 Group By a.Code,b.[TypeName]

) X Group By X.[TypeName],X.Code Order By X.Code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 15:09:00

post some data and explain what you want as output. otherwise we cant understand what you're asking for!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ArunPhilip
Starting Member

21 Posts

Posted - 2012-09-18 : 15:53:12

My Expected output is,
Count Type Code Sum
2 R ABC
2 S ABC 4
3 T DEF
2 U GHI
3 V JKL
2 W JKL 5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 15:53:40

where's the sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ArunPhilip
Starting Member

21 Posts

Posted - 2012-09-18 : 16:00:01

Expected Output
Count Type Code Sum
2 R ABC
2 S ABC 4
3 T DEF
2 U GHI
3 V JKL
2 W JKL 5

quote:

Sample Data In Table Type
Type Id Type Name
1 R
2 S
3 T
4 U
5 V
6 W

Sample Data In Table Code
Id CODE Type ID
1 ABC 1
2 ABC 2
3 DEF 3
4 GHI 4
5 JKL 5
6 JKL 6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 16:11:36

can you explain how you got those count and sum values?

as per data i cant understand how you got 3 etc as counts

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ArunPhilip
Starting Member

21 Posts

Posted - 2012-09-18 : 17:11:54

I appolagize for the confusion,
When I run the query


SELECT 
      Count(a.TypeID)CountOfTypes,b.[TypeName], a.Code
  FROM [Sets] a
  LEFT JOIN [Types] b
    ON a.[TypeID] = b.[TypeID]
WHERE AppCode <> 'ABC' AND AssetAppCode <> 'DEF' AND
a.TypeID IN (1,2) AND a.LocationID=64 Group By a.Code,b.[TypeName]

Here is the data that I get,
1 Server EFG
2 Server RRR
2 Desktop ABC
4 Server ABC

I would like to get a 4th column which will give me the sum for only those records that have the same Code name.
1 Server EFG
2 Server RRR
2 Desktop ABC
4 Server ABC 6

Does this help?

ArunPhilip
Starting Member

21 Posts

Posted - 2012-09-18 : 17:26:32

I have written 2 queries,
Query 1


SELECT 
      Count(a.HardwareTypeID)CountOfHWTypes,b.[HardwareTypeName], a.AssetAppCode
  FROM Assets a
  LEFT JOIN HardwareTypes b
    ON a.[HardwareTypeID] = b.[HardwareTypeID] 
WHERE AssetAppCode <> 'SH00' AND AssetAppCode <> 'SH01' AND
a.HardwareTypeID IN (54,57) AND a.CurrentLocationID=64 
GROUP BY a.AssetAppCode,b.[HardwareTypeName] Order By a.AssetAppCode

Query 2


SELECT Count(HardwareTypeID)SumOfHWType,AssetAppCode FROM Assets
		WHERE AssetAppCode <> 'SH00' AND AssetAppCode <> 'SH01' 
		AND HardwareTypeID IN (54,57) AND CurrentLocationID=64
		Group by AssetAppCode

Is there a way I can achive this just by using one query where I have SumOfHWType as the 4th column.
This might sound lame, but I am really stuck. Any help is much appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 17:28:27

[code]
SELECT CountOfTypes,[TypeName], Code,
CASE WHEN Seq>1 THEN SUM(CountOfTypes) OVER (PARTITION BY [Code]) END AS Col4
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY a.Code ORDER BY b.[TypeName]) AS Seq,
Count(a.TypeID)CountOfTypes,b.[TypeName], a.Code
FROM [Sets] a
LEFT JOIN [Types] b
ON a.[TypeID] = b.[TypeID]
WHERE AppCode <> 'ABC' AND AssetAppCode <> 'DEF' AND
a.TypeID IN (1,2) AND a.LocationID=64 Group By a.Code,b.[TypeName]
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ArunPhilip
Starting Member

21 Posts

Posted - 2012-09-18 : 17:45:42

Thanks visakh I will give it a try and let you know. Thanks once again for the help.

ArunPhilip
Starting Member

21 Posts

Posted - 2012-09-18 : 22:06:30

Appreciate the help and support. This is the exact solution that I was looking for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-19 : 12:08:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources