CODEWITHSUNDEEP
sqloracle-database
Derrick Rose
Derrick Rose

Reputation: 674

DISTINCT OR GROUP BY statements not removing duplicates?

What I am trying to do is have all client bills into one, so instead of "Whale Mart" coming up three times with different values I wish for "Whale Mart to have one total value, the total would be 25968.75

I tried using DISTINCT and GROUP BY to not show any repeated values.

DISTINCT:

SELECT DISTINCT CLIENTNAME AS "Client", HOURSWORKED*CHARGERATE AS "Total Billable"
FROM EMPLOYEE
WHERE CLIENTNAME IS NOT NULL
ORDER BY "Total Billable" DESC;

GROUP BY:

SELECT CLIENTNAME AS "Client", HOURSWORKED*CHARGERATE AS "Total Billable"
FROM EMPLOYEE
WHERE CLIENTNAME IS NOT NULL
GROUP BY CLIENTNAME, HOURSWORKED*CHARGERATE
ORDER BY "Total Billable" DESC;

Both methods give the same output:

Client                                             Total Billable
------------------------- ---------------------------------------
Whale Mart                                               19868.75
Flitter                                                     11271
Acme Corp.                                               9067.375
Flitter                                                      8625
Astro Electric.                                              5500
Acme Corp.                                                   4000
Lighting Unlimited                                           3675
Whale Mart                                                   3200
ABM Systems                                               3106.75
Whale Mart                                                   2900
ABC Logistics                                                2600
Acme Corp.                                                   2475
Astro Electric                                               2312
Flitter                                                      1845
ABM Systems                                                  1005
ABC Logistics                                                 900
Speedy Messengers                                             375
ABC Logistics                                                 345
Durham Tiles                                                  300
ABM Systems                                                   160

 20 rows selected

Upvotes: 1

Views: 722

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132700

You need:

SELECT CLIENTNAME AS "Client", SUM(HOURSWORKED*CHARGERATE) AS "Total Billable"
FROM EMPLOYEE
WHERE CLIENTNAME IS NOT NULL
GROUP BY CLIENTNAME
ORDER BY "Total Billable" DESC;

Upvotes: 1

randominstanceOfLivingThing
randominstanceOfLivingThing

Reputation: 18762

You may want to use an aggregate function:

SELECT CLIENTNAME AS "Client", SUM(HOURSWORKED*CHARGERATE) AS "Total Billable"
FROM EMPLOYEE
WHERE CLIENTNAME IS NOT NULL
GROUP BY CLIENTNAME
ORDER BY "Total Billable" DESC;

Upvotes: 3

Related Questions