Help With MS Query Sum Distinct
Thanks Joel.
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 ELIST
28 704 49.28
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 ELIST
20 472 33.04
Fewer lines and removing duplicates from the totals.
On Mar 9, 11:07*am, Joel wrote:
I still think you have to isolate the problem by eliminating some of the SQL.
*I think my previous code had a problem because I left out spaces between
Select, From, Where, and Group. *I also suspect that moving theSUMto the
end of the SQL will eliminate the duplicates. *hre is the code I modified
using you latest SQL and putting in the missing spaces (at the end of From,
where, group)
SelectSQL = "SELECT v_inputfiles.sName ," & _
* *"ciSE.ciSEInRefSource, " & _
* *"ciSE.ciSEInRefData "
FromSQL = "FROM chronosv2.dbo.ciSE ciSE, " & _
* *"chronosv2.dbo.v_inputfiles v_inputfiles "
WhereSQL = "WHERE ciSE.ciSEclient = v_inputfiles.sID AND " & _
* *"((ciSE.ciSEbillingmonth='2008-10')) AND " & _
* *"ciSE.ciSEID IN ( SELECTDISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE
ciSE) "
GroupSQL = "GROUP BY v_inputfiles.sName, " & _
* *"ciSE.ciSEInRefSource, " & _
* *"ciSE.ciSEInRefData "
HavingSQL = "HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') "
SumSQL = "SUM(ciSE.ciSEqty) AS 'Quantity', " & _
* *"SUM(ciSE.ciSEvalue) AS 'Value', " & _
* *"SUM(ciSE.ciSECharge) AS 'Charge' "
Sql = SelectSQL & FromSQL & WhereSQL & GroupSQL & HavingSQL & SumSQL
"Lexster" wrote:
Thank you so much for responding.
Unfortunately, your suggestion did not work. *I get an error message
"Incorrect Syntax near '&'. Statement(s) could not be prepared."
I have modified my top statement as:
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
DISTINCTciSE.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
However I still get more rows listed from the v_inputfiles.Sname than
I want. *Another dilemma is that when I try tosumQuanty, Value, and
Charge, the total will include duplicates even though I am trying to
rid duplicates in my Where IN ( SELECTDISTINCTciSE.ciSEID FROM
chronosv2.dbo.ciSE ciSE) statement.
This is the query I tried to use - it returns summary lines for
v_inputfiles.name (which is what I want) but it includes duplicates in
the totals for Quantity, value, Charge:
SELECT v_inputfiles.sName ,SUM(ciSE.ciSEqty) AS 'Quantity',SUM
(ciSE.ciSEvalue) AS 'Value',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
DISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE)
GROUP BY v_inputfiles.sName, ciSE.ciSEInRefSource, ciSE.ciSEInRefData
HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'')
I am using MS Query in Excel.
Thanks again for responding... you're the only one so far.
All the best,
Lex
On Mar 5, 1:03 pm, Joel wrote:
People forget the SQL's are long strings *try this
SelectSQL = _
* *"SELECTDISTINCTv_inputfiles.sName AS 'Client Name'," & _
* *"ciSE.ciSEqty," & _
* *"ciSE.ciSEvalue," & _
* *"ciSE.ciSECharge," & _
* *"ciSE.ciSEInRefSource," & _
* *"ciSE.ciSEInRefData"
FromSQL = _
* *"FROM chronosv2.dbo.ciSE ciSE," & _
* *"chronosv2.dbo.v_inputfiles v_inputfiles"
WhereSQL = _
* *"WHERE ciSE.ciSEclient = v_inputfiles.sID AND " & _
* *"((ciSE.ciSEbillingmonth='2008-10'))"
GroupSQL = _
* *"GROUP BY v_inputfiles.sName, " & _
* *"ciSE.ciSEID, " & _
* *"ciSE.ciSEqty, " & _
* *"ciSE.ciSEvalue, " & _
* *"ciSE.ciSECharge, " & _
* *"ciSE.ciSEInRefSource," & _
* *"ciSE.ciSEInRefData"
HavingSQL = _
* *"HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST')"
Sql = SelectSQL & FromSQL & WhereSQL & HavingSQL
"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.
|