Help With MS Query Sum Distinct
 

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

Help With MS Query Sum Distinct
 

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

Help With MS Query Sum Distinct
 

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.



On Mar 5, 9:15*pm, Hong Quach
wrote:
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 (SELECTDISTINCT
* * * * * * * * 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 aboutSUMof data but I don't see anySUM() function being use in the SELECT clause
of your query; *Second, when you remove the ciSE.ciSEID AS 'Transaction ID'
in the SELECTDISTINCTclause, the result should only be fewer row and not
more (less features todistinctbetween 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:

SELECTDISTINCTv_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 theDistinct,
mysumincludes all the duplicates.

My goal is to have thesumof 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