Count Distribution

60 pts.
Tags:
Microsoft Excel
SQL
From an input table consisting of YEAR and COUNT, e.g.

YEAR COUNT

1999 3

2000 4

2000 7

2000 15

Can a Pivot Table or SQL Query be constructed to output a Yearly Count Distribution? e.g.

YEAR 1->9 10->19 20->29

1999   1

2000   2        1

Thanks, JaG



Software/Hardware used:
SQL, Excel

Answer Wiki

Thanks. We'll let you know when a new response is added.

This is SQL for DB2 for z/OS code that will do what you want.

You may have to change the syntax if you are using SQL in some RDBMS other than DB2 for z/OS. Oracle, for example, uses DECODE instead of CASE.

Also, you will need to repeat the CASE statement for how ever many categories you may have.

<pre>
SELECT A.YR, SUM (A.A) AS “1->9″
, SUM (A.B) AS “10->19″
, SUM (A.C) AS “20->…”
FROM (
SELECT YR
,CASE WHEN CNT < 10
THEN 1 ELSE 0
END AS A
,CASE WHEN CNT > 9 AND CNT < 20
THEN 1 ELSE 0
END AS B
,CASE WHEN CNT > 19
THEN 1 ELSE 0
END AS C
FROM TTEST ) AS A
GROUP BY YR;
</pre>

here is my test data.
<pre> CREATE TABLE TTEST (YR INT , CNT INT);
INSERT INTO TTEST VALUES (2007 , 1);
INSERT INTO TTEST VALUES (2008 , 7);
INSERT INTO TTEST VALUES (2008 ,10);
INSERT INTO TTEST VALUES (2008 ,12); </pre>

here is my result.
<pre>
YR 1->9 10->19 20->…
———+———+———+———+———+———+–
2007 1 0 0
2008 1 2 0
</pre>

By the way, I get paid for doing this sort of work. :-)

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Oracle doesn't use DECODE instead of CASE. You could use both, but DECODE usually simplifies tasks like this.
    69,175 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following