How about

SELECT dept,;
       COUNT(*) AS Num_emp,;
       SUM(IIF(salary>=60000,1,0)) AS rich,;
       SUM(IIF(salary<60000,1,0)) as poor ;
  FROM myTable ;
  GROUP BY dept

>Hi,
>
>I have following table and I need a query to get a count of employees broken down by department, and within each department by whether they are “rich” or “poor”. “Rich” is defined as having a Salary of at least $60000; everyone else is “poor”.
>Can anybody help me ? Thanks in advance.
>
>+------+------------+----------+------------+-----------+
>| ENo | LName | FName | Dept | Salary |
>+------+------------+----------+------------+-----------+
>| 0876 | OReilly | Walter | Sales | 31900.00 |
>| 0987 | Dancer | April | Payroll | 65000.00 |
>| 1123 | MacMaster | Kate | Marketing | 83100.00 |
>| 1124 | Klinger | Maxwell | Systems | 32080.00 |
>| 1243 | Bellingham | Myrtle | Payroll | 65000.00 |
>| 2133 | Bell | Fiona | Systems | 34100.98 |
>| 2210 | Howell | Lovey | Sales | 30100.29 |
>| 2316 | Winchester | Charles | Sales | 30900.99 |
>| 2435 | Muldoon | Francis | Personnel | 8900.00 |
>| 2875 | Douglas | Ernest | Accounting | 109000.00 |
>| 2980 | Glympshire | Herman | Marketing | 118700.59 |
>| 3109 | Toddy | Gunther | Personnel | 11100.55 |
>| 3153 | Smacker | Woodrow | Accounting | 42080.00 |
>| 3210 | Bellingham | Lester | Payroll | 37000.50 |
>| 3285 | Moto | Kentaro | Systems | 28000.00 |
>| 3289 | Hinkley | Roy | Systems | 53400.80 |
>| 3316 | Archer | Morton | Sales | 23100.00 |
>| 3345 | Summers | Marianne | Marketing | 23000.00 |
>| 3444 | McElreath | James | Payroll | 30000.00 |
>| 3457 | Bell | Jorge | Sales | 23000.00 |
>| 3459 | Chan | Charles | Personnel | 28700.00 |
>| 4532 | Kuryakin | Ilya | Accounting | 34080.00 |
>| 4538 | Hilton | Susan | Payroll | 38080.00 |
>| 4563 | McArthur | James | Systems | 45080.87 |
>| 5409 | Macafee | Earl | Sales | 43000.00 |
>| 5487 | Grant | Ginger | Accounting | 45600.90 |
>| 5543 | McElroy | Arnold | Systems | 45080.00 |
>| 5558 | Bellini | Giorgio | Marketing | 21900.00 |
>| 5644 | Spade | Samuel | Systems | 26500.00 |
>| 5672 | Del Bello | Adam | Personnel | 56800.00 |
>| 6549 | Blake | Henry | Systems | 24000.00 |
>| 6740 | Solo | Napoleon | Sales | 24100.88 |
>| 6789 | Grumby | Jonas | Payroll | 24900.00 |
>| 7611 | Pierce | Benjamin | Accounting | 54170.90 |
>| 7711 | Mackle | Fearghal | Systems | 33190.00 |
>| 8135 | Bell | Gertrude | Sales | 43000.00 |
>| 8732 | Hoolihan | Margaret | Systems | 34200.80 |
>| 8796 | Potter | Sherman | Accounting | 34620.00 |
>| 8934 | Archer | Miles | Sales | 22450.20 |
>| 9087 | Smacker | Wilton | Accounting | 46000.00 |
>| 9871 | Hunnicut | Beejay | Personnel | 23000.00 |
>| 9876 | Smart | Maxwell | Accounting | 36500.95 |
>| 9987 | Marlowe | Philip | Marketing | 34300.08 |
>| 9999 | Burns | Francis | Marketing | 67535.80 |
>+------+------------+----------+------------+-----------+