Joining 2 Counts Help (Thank you in advance)

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

General SQL Server Forums

New to SQL Server Programming

Joining 2 Counts Help (Thank you in advance)

Author

Topic

Dani_87
Starting Member

14 Posts

Posted - 2012-03-03 : 19:46:53

I 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

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-03-03 : 21:44:37

Using case, you can get the 2 count with just 1 time accessing the source. If still need help in detail, just mention it ...

Dani_87
Starting Member

14 Posts

Posted - 2012-03-04 : 05:36:14

I do not need the count output as much as the frequency ( % ).
The % is the main part.
I assumed you could do that with case but I would not have a clue how.
Could you please explain in a little more detail, idealy display it in code.

Thank you.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-03-04 : 12:13:40

I may understand your requirement incorrectly ... But just think that the frequency is based on count, if you have count, you also have frequency ...

I may be wrong, so provide more sample data and expected output. From the data and your code, I can find out exactly what is your requirement, then provide help if I can or many other people can help you too.

Many unsolved problems in this forum are ones that people do not understand the requirement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-04 : 14:22:37

you can simply do like this


SELECT COALESCE(q1.Person_id,q2.Person_id) AS Person_id,
Payment_Style,
q1.Cnt,
q1.[% of Payment Style],
MARK,
q2.Cnt,
[% of MARK FAILS P]
FROM(your first query) q1
FULL OUTER JOIN 
(your second query) q2
ON q2.Person_id = q1.Person_id

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

Dani_87
Starting Member

14 Posts

Posted - 2012-03-04 : 19:20:31

My aplogise, here is more info.

This is my table

PERSON_ID	YEAR	TERM	BANK	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

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-07 : 19:52:42

can you explain how you got values 63,3 etc?

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

Chloe_19
Starting Member

44 Posts

Posted - 2012-03-08 : 18:00:21

Sorry its ment to be 66%, 33%
Sime conversion to percentage, from the count, 2-1, 63 - 33 (ignoring decimals)

Sorry for late reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 18:17:00

[code]
SELECT PERSON_ID,PAY,COUNT(*) AS PAY_CNT,COUNT(*) * 1.0/BnkCnt AS PAY%,BANK,BnkCnt
FROM (SELECT *,COUNT(1) OVER (PARTITION BY PERSON_ID,YEAR,BANK) AS BnkCnt FROM query)q
GROUP BY PERSON_ID,PAY,BANK,BnkCnt
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources