Hi Lexster,

It is not easy to understand what you re trying to do without looking at the
data. If the current query gives you the desired result, and you only want
to remove column from the result then one way to do this is to treat this
query as a table and query only the column you want (such as not to include
ciSEID in the outer select statement).

Try this query out and see if it is what you need.

SELECT
v_inputfiles.sName AS 'Client Name',
ciSE.ciSEqty,
ciSE.ciSEvalue,
ciSE.ciSECharge,
ciSE.ciSEInRefSource,
ciSE.ciSEInRefData
FROM (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')) AS TB1

Couple confusion point in your post: First, In your post, you mention about
SUM of data but I don't see any SUM() function being use in the SELECT clause
of your query; Second, when you remove the ciSE.ciSEID AS 'Transaction ID'
in the SELECT DISTINCT clause, the result should only be fewer row and not
more (less features to distinct between rows) unless you also removed it from
the GROUP BY clause. Thing in the GROUP BY clause doesn't have to be listed
in the SELECT clause.

Hong Quach

"Lexster" wrote:

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