I am having trouble get the numbers that I need. I have a table that records
positions and the action that happened at that positon and the operator that
caused the action. I need to total the amounts per Operator per Action Code.
I tried to use GROUP BY but it only gave me one operator for each LotID. Thi
s
was the statement I used:
SELECT DISTINCT LotID, UserName, SUM(EncoderpositionDetectionEnd1) AS [End
1], ActionCode
FROM dbo.OperatorData
GROUP BY UserName, ActionCode, LotID
ORDER BY LotID
The following is an example of the data that is stored in the table.
LotID Operator Position Action Code
826O3 Priscilla 923797449 11
826O3 Priscilla 926950347 10
826O3 Priscilla 923797449 11
826O3 Priscilla 926950347 10
A1073 Susan 2946519574 11
826O3 Priscilla 960248867 11
82603 Gloria 885226642 10
82603 Gloria 893352901 11
82603 Angela 924485652 11
82603 Gloria 896646927 10
826O3 Priscilla 960248867 11
A1073 Susan 2946519574 11
82603 Angela 927628980 10
82603 Gloria 885226642 10
82603 Gloria 893352901 11
82603 Gloria 896646927 10
A1073 Carolyn 2915880805 10
82603 Angela 924485652 11Can you clarify this? The columns in your data do not match the columns in
your query. Also, do you need to sum (add stuff up) our count the rows? Also
,
you say you need the amounts per operator per action code but your group by
includes lot id.
"A.B." wrote:
> Hi,
> I am having trouble get the numbers that I need. I have a table that recor
ds
> positions and the action that happened at that positon and the operator th
at
> caused the action. I need to total the amounts per Operator per Action Cod
e.
> I tried to use GROUP BY but it only gave me one operator for each LotID. T
his
> was the statement I used:
> SELECT DISTINCT LotID, UserName, SUM(EncoderpositionDetectionEnd1) AS [End
> 1], ActionCode
> FROM dbo.OperatorData
> GROUP BY UserName, ActionCode, LotID
> ORDER BY LotID
> The following is an example of the data that is stored in the table.
> LotID Operator Position Action Co
de
> 826O3 Priscilla 923797449 11
> 826O3 Priscilla 926950347 10
> 826O3 Priscilla 923797449 11
> 826O3 Priscilla 926950347 10
> A1073 Susan 2946519574 11
> 826O3 Priscilla 960248867 11
> 82603 Gloria 885226642 10
> 82603 Gloria 893352901 11
> 82603 Angela 924485652 11
> 82603 Gloria 896646927 10
> 826O3 Priscilla 960248867 11
> A1073 Susan 2946519574 11
> 82603 Angela 927628980 10
> 82603 Gloria 885226642 10
> 82603 Gloria 893352901 11
> 82603 Gloria 896646927 10
> A1073 Carolyn 2915880805 10
> 82603 Angela 924485652 11
>|||SELECT DISTINCT LotID, UserName 'Operator',
SUM(EncoderpositionDetectionEnd1) AS [End
The LotID in order to connect the results to another query I have that gives
me the Lots that were run last w
. Also I need the Sum of the rows."Kathi Kellenberger" wrote:
> Can you clarify this? The columns in your data do not match the columns in
> your query. Also, do you need to sum (add stuff up) our count the rows? Al
so,
> you say you need the amounts per operator per action code but your group b
y
> includes lot id.
>
>
> "A.B." wrote:
>|||With your query, you should get a row for every possible combination of
lotID, operator and action code. I'm not sure if that's what you are after.
"A.B." wrote:
> SELECT DISTINCT LotID, UserName 'Operator',
> SUM(EncoderpositionDetectionEnd1) AS [End
> The LotID in order to connect the results to another query I have that giv
es
> me the Lots that were run last w
. Also I need the Sum of the rows.> "Kathi Kellenberger" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. And your narrative is useless.|||That is what i want but this is an example of the results that I am getting:
62324 Pamela 9969832955 10
62324 Pamela 19966076115 11
62332 Susan 9641299760 11
62332 Teresa 9633011910 11
62334 Carolyn 9978382505 10
62334 Carolyn 19983575455 11
62334 Melissa 9719717360 10
I am only getting a one result for certain lots and several for others.
"Kathi Kellenberger" wrote:
> With your query, you should get a row for every possible combination of
> lotID, operator and action code. I'm not sure if that's what you are afte
r.
>
>
> "A.B." wrote:
>|||A.B.,
Kathy says you'll get one row for each combination of lotID, operator,
and action code,
and you say "That is what i want". This is exactly what you are
getting. The combinations
in your results are
(62324, Pamela, 10)
(62324, Pamela, 11)
(62332, Susan, 11)
(62332, Teresa, 11)
(62334, Carolyn, 10)
(62334, Carolyn, 11)
(62334, Melissa, 10)
You have more than one name and/or action code for some lotID values,
so you will get more than one row for those values. For example, for lotID
62334, you have information for Melissa with action code 10, and you have
information for Carolyn with action codes both 10 and 11. If you want only
one row for this lotID, do you want it to say Melissa or Carolyn, and do you
want the action code to be 10 or 11? You need to be more specific about
what your result is supposed to be.
Steve Kass
Drew University
A.B. wrote:
>That is what i want but this is an example of the results that I am getting
:
> 62324 Pamela 9969832955 10
> 62324 Pamela 19966076115 11
> 62332 Susan 9641299760 11
> 62332 Teresa 9633011910 11
> 62334 Carolyn 9978382505 10
> 62334 Carolyn 19983575455 11
> 62334 Melissa 9719717360 10
>I am only getting a one result for certain lots and several for others.
>"Kathi Kellenberger" wrote:
>
>|||No, because I am only getting the operator Pamela for Lot 62324 when actuall
y
there is four or five operators.
"Steve Kass" wrote:
> A.B.,
> Kathy says you'll get one row for each combination of lotID, operator,
> and action code,
> and you say "That is what i want". This is exactly what you are
> getting. The combinations
> in your results are
> (62324, Pamela, 10)
> (62324, Pamela, 11)
> (62332, Susan, 11)
> (62332, Teresa, 11)
> (62334, Carolyn, 10)
> (62334, Carolyn, 11)
> (62334, Melissa, 10)
> You have more than one name and/or action code for some lotID values,
> so you will get more than one row for those values. For example, for lotI
D
> 62334, you have information for Melissa with action code 10, and you have
> information for Carolyn with action codes both 10 and 11. If you want onl
y
> one row for this lotID, do you want it to say Melissa or Carolyn, and do y
ou
> want the action code to be 10 or 11? You need to be more specific about
> what your result is supposed to be.
> Steve Kass
> Drew University
>
> A.B. wrote:
>
>|||Ah. When you said "only one" for some and "several" for others, I
thought the problem was the "several", not the "one". ;)
My guess is that you are not showing us the entire query, since if there
is a row with LotID 62324 and operator <> 'Pamela' in dbo.OperatorData,
the result of
SELECT DISTINCT
LotID,
UserName 'Operator',
SUM(EncoderpositionDetectionEnd1) AS [End 1],
ActionCode
FROM dbo.OperatorData
GROUP BY UserName, ActionCode, LotID
ORDER BY LotID
will definitely include a row showing 62324 with another operator.
Perhaps you
are noting the omission only after this query is used in a larger one, maybe
with an inner join that should be a left join - I can't be sure.
If you are certain that this is your query and that results are missing,
please show us both the results of this query and the result of
SELECT TOP 10
LotID,
UserName, 'Operator',
EncoderpositionDetectionEnd1,
ActionCode
FROM dbo.OperatorData
WHERE LotID = '62324'
AND UserName <> 'Pamela'
-- optionally add ORDER BY something...
SK
A.B. wrote:
>No, because I am only getting the operator Pamela for Lot 62324 when actual
ly
>there is four or five operators.
>"Steve Kass" wrote:
>
>|||I had a date in the where clause to make my results alot smaller and by
taking the date out of the where clause it allowed me to see all of the
operators. I am not sure why this happened but it is working now. Thanks for
your help man.
"Steve Kass" wrote:
> Ah. When you said "only one" for some and "several" for others, I
> thought the problem was the "several", not the "one". ;)
> My guess is that you are not showing us the entire query, since if there
> is a row with LotID 62324 and operator <> 'Pamela' in dbo.OperatorData,
> the result of
> SELECT DISTINCT
> LotID,
> UserName 'Operator',
> SUM(EncoderpositionDetectionEnd1) AS [End 1],
> ActionCode
> FROM dbo.OperatorData
> GROUP BY UserName, ActionCode, LotID
> ORDER BY LotID
> will definitely include a row showing 62324 with another operator.
> Perhaps you
> are noting the omission only after this query is used in a larger one, may
be
> with an inner join that should be a left join - I can't be sure.
> If you are certain that this is your query and that results are missing,
> please show us both the results of this query and the result of
> SELECT TOP 10
> LotID,
> UserName, 'Operator',
> EncoderpositionDetectionEnd1,
> ActionCode
> FROM dbo.OperatorData
> WHERE LotID = '62324'
> AND UserName <> 'Pamela'
> -- optionally add ORDER BY something...
> SK
> A.B. wrote:
>
>
No comments:
Post a Comment