Asad Mahmood Asad Mahmood - 2 months ago 7
SQL Question

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

Answer

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;
Comments