Help With MS Query Sum Distinct

[Lexster]

Hi everyone.

I hope someone can help me out with a SQL formula I am using with MS
Query in Excel.

This is what I have right now:

SELECT DISTINCT v_inputfiles.sName AS 'Client Name', ciSE.ciSEID AS
'Transaction ID', ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge,
ciSE.ciSEInRefSource, ciSE.ciSEInRefData
FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles
WHERE ciSE.ciSEclient = v_inputfiles.sID AND
((ciSE.ciSEbillingmonth='2008-10'))
GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty,
ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource,
ciSE.ciSEInRefData
HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST')

What I need help with is adjusting this SQL command so that I get rid
ciSE.ciSEID AS 'Transaction ID' but still maintain the same results. I
used the ciSE.ciSEID field because it was used to filter out
duplicates. However when I take this field out and use the Distinct,
my sum includes all the duplicates.

My goal is to have the sum of ciSE.ciSEqty, ciSE.ciSEvalue,
ciSE.ciSECharge grouped by v_inputfiles.sName AS 'Client Name' - which
will bring over fewer rows.

I would greatly appreciate any suggestions and help on this.
Thank you in advance.
Lex

 

[Lexster]

Thank you Hong-

I tried your SQL statements and I got an error saying, "Could not add
the table '(Select'.

Here to help clarify what I am trying to do...
sName ciSEID ciSEqty ciSEvalue ciSECharge ciSEInRefSource
ciSEInRefData
Client 1 37769983 2 40 2.8 TM FB08SPLASH
Client 1 37769990 2 40 2.8 TM FB08SPLASH
Client 1 37770017 6 192 13.44 TM ELIST
Client 1 37770035 2 40 2.8 TM ELIST
Client 1 37770036 2 40 2.8 TM ELIST
Client 1 37770046 2 40 2.8 TM FB08SPLASH
Client 1 37770074 4 80 5.6 TM ELIST
Client 1 37769983 2 40 2.8 TM FB08SPLASH
Client 1 37770017 6 192 13.44 TM ELIST
28 704 49.28

When I run this query (I have modified my original query):
SELECT v_inputfiles.sName AS 'Client Name', ciSE.ciSEqty AS
'Quantity', ciSE.ciSEvalue AS 'Value', ciSE.ciSECharge AS 'Charge',
ciSE.ciSEInRefSource AS 'Source', ciSE.ciSEInRefData AS 'Program'
FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles
WHERE ciSE.ciSEclient = v_inputfiles.sID AND
((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT
DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE)
GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty ,
ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource,
ciSE.ciSEInRefData
HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<>'')
ORDER BY v_inputfiles.sName

This query takes out the duplicates in ciSEID - you will notice there
are two lines missing.
sName ciSEID ciSEqty ciSEvalue ciSECharge ciSEInRefSource
ciSEInRefData
Client 1 37769983 2 40 2.8 TM FB08SPLASH
Client 1 37769990 2 40 2.8 TM FB08SPLASH
Client 1 37770017 6 192 13.44 TM ELIST
Client 1 37770035 2 40 2.8 TM ELIST
Client 1 37770036 2 40 2.8 TM ELIST
Client 1 37770046 2 40 2.8 TM FB08SPLASH
Client 1 37770074 4 80 5.6 TM ELIST
20 472 33.04


I use the following query:
SELECT v_inputfiles.sName , SUM (ciSE.ciSEqty) AS 'Quantity',SUM
(ciSE.ciSEvalue) AS 'Quantity', SUM (ciSE.ciSECharge) AS 'Charge',
ciSE.ciSEInRefSource, ciSE.ciSEInRefData
FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles
WHERE ciSE.ciSEclient = v_inputfiles.sID AND
((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT
DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE)
GROUP BY v_inputfiles.sName, ciSE.ciSEInRefSource, ciSE.ciSEInRefData
HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<>'')

This includes the duplicates in the Sum totals.
sName ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData
Client 1 8 160 11.2 TM FB08SPLASH
Client 1 20 544 38.8 TM FB08SPLASH
28 704 33.04
Notice the totals add up to the same as the first example.

My end goal is to get this:
sName ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData
Client 1 6 120 8.4 TM FB08SPLASH
Client 1 14 352 24.6 TM FB08SPLASH
20 472 33.04

Fewer lines and removing duplicates from the totals.

Any help would be much appreciated.