March 3, 2012 at 5:58 pm
am trying to join these 2 frequency & counts (Payment and MARK)by ID
Indicidualy they work. But I would like them as one query.
So should look something like
ID Payment CNT Payment % MARK MARK_CNT MARK FAILS%
50 7 2 85% F 2 89%
Thank for any help offered.
--Payment
SELECT ab.Person_id
,ab.Payment_Style
,ab.Cnt
,CAST(ab.Cnt AS FLOAT) / CAST(SUM(ab.Cnt) OVER(PARTITION BY ab.oua_id) AS FLOAT)* 100 "% of Payment Style"
FROM(
SELECT a.Person_id
,a.Payment_Style
,COUNT(*) Cnt
FROM TABLE_6 A
WHERE a.Payment_Style in ('FEE-HELP','CASH')
GROUP BY a.Person_id,
a.Payment_Style
) ab
ORDER BY ab.Person_id
--MARK
SELECT ab.Person_id
,ab.MARK
,ab.Cnt
,CAST(ab.Cnt AS FLOAT) / CAST(SUM(ab.Cnt) OVER(PARTITION BY ab.oua_id) AS FLOAT)* 100 "% of MARK FAILS P"
FROM(
SELECT A.Person_id
,a.MARK
,COUNT(*) Cnt
FROM TABLE_6 A
WHERE a.MARK in ('W','NA','Z','N')
GROUP BY A.Person_id, a.MARK
) ab
ORDER BY ab.PERSON_id
March 4, 2012 at 3:13 am
You have different column names in GROUP BY. Would it be possible to have some sample data in a ready to use format (see the first link in my signature for details) together with your expected result so we have something "to play with"?
March 4, 2012 at 5:31 pm
My aplogise, here is more info.
This is my table
PERSON_IDYEARTERMBANKPAYRATING
1020076NABCASHB
1020077NABCredit G
1020077NABCredit B
10120126POLCASHP
106 2006 3 NAL HEP 1
106 2007 3 NAL CASH C
106 2007 3 NAL HEP D
I want THIS
PERSON_ID PAY PAY_CNT PAY % BANK BANK_CNT BANK %
10 CASH 2 63 NAB 3 100
10 CREDIT 1 33 NAB 3 100
101 CASH 1 100 POL 1 100
ETC
March 5, 2012 at 2:46 am
This was removed by the editor as SPAM
March 5, 2012 at 6:39 am
Dani_87 (3/4/2012)
My aplogise, here is more info.This is my table
PERSON_IDYEARTERMBANKPAYRATING1020076NABCASHB
1020077NABCredit G
1020077NABCredit B
10120126POLCASHP
106 2006 3 NAL HEP 1
106 2007 3 NAL CASH C
106 2007 3 NAL HEP D
if you really want to us to help you then please make it easy for us....:-)
this is the preferred method...we can cut and paste in SSMS
USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_6]') AND type in (N'U'))
DROP TABLE [dbo].[Table_6]
GO
CREATE TABLE [dbo].[Table_6](
[PERSON_ID] [int] NULL,
[YEAR] [int] NULL,
[TERM] [int] NULL,
[RANK] [nvarchar](50) NULL,
[PAY] [nvarchar](50) NULL,
[RATING] [nvarchar](50) NULL
)
GO
INSERT INTO [dbo].[Table_6]([PERSON_ID], [YEAR], [TERM], [RANK], [PAY], [RATING])
SELECT 10, 2007, 6, N'NAB', N'CASH', N'B' UNION ALL
SELECT 10, 2007, 7, N'NAB', N'Credit', N'G' UNION ALL
SELECT 10, 2007, 7, N'NAB', N'Credit', N'B' UNION ALL
SELECT 101, 2012, 6, N'POL', N'CASH', N'P' UNION ALL
SELECT 106, 2006, 3, N'NAL', N'HEP', N'1' UNION ALL
SELECT 106, 2007, 3, N'NAL', N'CASH', N'C' UNION ALL
SELECT 106, 2007, 3, N'NAL', N'HEP', N'D'
SELECT * FROM Table_6
this gives us your "table" as you presented....but this doesnt match your original two queries...(try running your code against my table)
PERSON_ID YEAR TERM RANK PAY RATING
----------- ----------- ----------- -------- -------- --------
10 2007 6 NAB CASH B
10 2007 7 NAB Credit G
10 2007 7 NAB Credit B
101 2012 6 POL CASH P
106 2006 3 NAL HEP 1
106 2007 3 NAL CASH C
106 2007 3 NAL HEP D
Can you please post the correct table definition and data.
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply