hi guys
I am trying to get the total count and total sum per salesperson but its not working in my WITH query
Currently I have:
WITH CALC1 AS (SELECT B.OTSMAN AS SALESMAN, COUNT(B.ONINUM) AS COUNT, SUM(B.OVSVAL) AS SVAL FROM @
[email protected] AS B WHERE B.OCSGRP <> 'INT' AND B.OCSGRP <> '
INX' AND B.OCPSBR IN (@OCPSBR@) AND B.ICMATX <> '705' AND B.OCSGRP NOT LIKE 'S/%' GROUP BY B.OTSMAN, B.ONINUM ORDER BY B.OTSMAN ASC)
SELECT
SALESMAN,
COUNT,
SVAL
FROM CALC1
ORDER BY SALESMAN
THE RESULT IS:
AM 1 79.03
AM 40 1337.70
AM 48 4627.15
AM 42 2727.40
AM 1 111.79
AM 1 1110.00
AM 52 3018.77
How do i change my query so I only get one record with the total count and value per salesperson?
AM 1 13,011.84