looking for help to built SQL - SQL Server Forums

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)

looking for help to built SQL

Author

Topic

jennifer4474
Starting Member

5 Posts

Posted - 2012-05-31 : 05:07:05

My table and data,


USE [TUNEDB]
GO
/****** Object:  Check [CK_payer_payerTypeDescrp]    Script Date: 05/31/2012 16:52:51 ******/
IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_payer_payerTypeDescrp]') AND parent_object_id = OBJECT_ID(N'[dbo].[t1]'))
BEGIN
IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_payer_payerTypeDescrp]') AND parent_object_id = OBJECT_ID(N'[dbo].[t1]'))
ALTER TABLE [dbo].[t1] DROP CONSTRAINT [CK_payer_payerTypeDescrp]

END
GO
/****** Object:  Table [dbo].[t1]    Script Date: 05/31/2012 16:52:51 ******/
IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_payer_payerTypeDescrp]') AND parent_object_id = OBJECT_ID(N'[dbo].[t1]'))
ALTER TABLE [dbo].[t1] DROP CONSTRAINT [CK_payer_payerTypeDescrp]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t1]') AND type in (N'U'))
DROP TABLE [dbo].[t1]
GO
/****** Object:  Table [dbo].[t1]    Script Date: 05/31/2012 16:52:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t1]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[t1](
	[payerType] [varchar](200) NOT NULL,
	[amt] [decimal](10, 2) NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[t1] ([payerType], [amt]) VALUES (N'koperasi', CAST(479.00 AS Decimal(10, 2)))
INSERT [dbo].[t1] ([payerType], [amt]) VALUES (N'koperasi', CAST(651.60 AS Decimal(10, 2)))
INSERT [dbo].[t1] ([payerType], [amt]) VALUES (N'koperasi', CAST(2794.50 AS Decimal(10, 2)))
INSERT [dbo].[t1] ([payerType], [amt]) VALUES (N'syarikat', CAST(500.00 AS Decimal(10, 2)))
INSERT [dbo].[t1] ([payerType], [amt]) VALUES (N'syarikat', CAST(25.00 AS Decimal(10, 2)))
INSERT [dbo].[t1] ([payerType], [amt]) VALUES (N'individu', CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[t1] ([payerType], [amt]) VALUES (N'individu', CAST(26.00 AS Decimal(10, 2)))
INSERT [dbo].[t1] ([payerType], [amt]) VALUES (N'individu', CAST(500.00 AS Decimal(10, 2)))
/****** Object:  Check [CK_payer_payerTypeDescrp]    Script Date: 05/31/2012 16:52:51 ******/
IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_payer_payerTypeDescrp]') AND parent_object_id = OBJECT_ID(N'[dbo].[t1]'))
ALTER TABLE [dbo].[t1]  WITH CHECK ADD  CONSTRAINT [CK_payer_payerTypeDescrp] CHECK  (([payerType]='koperasi' OR [payerType]='syarikat' OR [payerType]='individu'))
GO
IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_payer_payerTypeDescrp]') AND parent_object_id = OBJECT_ID(N'[dbo].[t1]'))
ALTER TABLE [dbo].[t1] CHECK CONSTRAINT [CK_payer_payerTypeDescrp]
GO

So far, I've


select payerType, SUM(amt) as amt from dbo.t1
group by payerType

-result-
payerType	amt
------------------------------
individu	626.00
koperasi	3925.10
syarikat	525.00

My requirement, I need to procude the result as following,


payerType	payTypeNo	percentage	amt
individu	3	37.5	626.00
koperasi	3	37.5	3925.10
syarikat	2	25	525.00

Can help?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-31 : 05:40:53

select payerType, payTypeNo = count(*), SUM(amt) as amt,
from dbo.t1
group by payerType

how is the percentage calculated?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jennifer4474
Starting Member

5 Posts

Posted - 2012-05-31 : 13:57:47

quote:
Originally posted by nigelrivett

select payerType, payTypeNo = count(*), SUM(amt) as amt,
from dbo.t1
group by payerType

how is the percentage calculated?

Sir,

The formula is payTypeNo / no of all row in t1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 15:48:23

[code
select payerType, payTypeNo = count(*), SUM(amt) as amt, SUM(amt)/COUNT(*) AS [percentage]
from dbo.t1
group by payerType
[/code]

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

jennifer4474
Starting Member

5 Posts

Posted - 2012-06-03 : 02:48:53

quote:

Originally posted by visakh16


select payerType, payTypeNo = count(*), SUM(amt) as amt, SUM(amt)/COUNT(*) AS [percentage]
from dbo.t1
group by payerType

It's return,

payerType	payTypeNo	amt	percentage
individu	3	626.00	208.666666
koperasi	3	3925.10	1308.366666
syarikat	2	525.00	262.500000

It's suppose,

payerType	payTypeNo	amt         percentage
individu	3	        626.00               37.5
koperasi	3	        3925.10              37.5	
syarikat	2	        525.00               25

Sir,

The formula is payTypeNo / no of all row in t1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-03 : 15:21:49

the do change accordingly



select payerType, payTypeNo = count(*), SUM(amt) as amt, COUNT(*) * 1.0/COUNT(1) OVER () AS [percentage]
from dbo.t1
group by payerType

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

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-03 : 17:46:37

[code]

select payerType, SUM(amt)as amt,Count(1)payTypeNo ,
left(cast(Count(1)as decimal(10))/ ( select Count(1)from t1)*100,4) as percentage
from dbo.t1
group by payerType

select payerType, payTypeNo = count(*), SUM(amt) as amt, left((COUNT(*) * 1.0/(select COUNT(1) from dbo.t1) )*100 ,4)AS [percentage]
from dbo.t1
group by payerType

[/code]

Vijay is here to learn something from you guys.

jennifer4474
Starting Member

5 Posts

Posted - 2012-06-03 : 20:02:49

thanks to Mr Visakh, and Mr Vijays3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-03 : 22:36:41

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources