May 6, 2009 at 11:57 am
Hello,
I have a query in which I want to insert my count statement.
This is my query:
SELECT
CRM_StatusCodes.TRANSACTION_ID
,CRM_StatusCodes.CREATED_AT
,CRM_StatusCodes.USER_STATUS, CRM_Serials.SERIAL
,CRM_PartsLabor.DESCRIPTION
,CRM_Orders.PRIORITY_DESCRIPTION
,CRM_Orders.PRIORITY
/*,CRM_StatusCodes.START_DATE*/
/*,CRM_StatusCodes.END_DATE*/
,CRM_CodeTexts.ABCDE_CODE_DESC
,CRM_Partners.DESCRIPTION AS [Function]
,CRM_Partners.ADDRESS
FROM
CRM_StatusCodes INNER JOIN
CRM_Serials ON CRM_StatusCodes.ORDER_ID = CRM_Serials.ORDER_ID INNER JOIN
CRM_SubjectReferences ON CRM_StatusCodes.TRANSACTION_ID = CRM_SubjectReferences.TRANSACTION_ID INNER JOIN
CRM_PartsLabor ON CRM_SubjectReferences.TRANSACTION_ID = CRM_PartsLabor.TRANSACTION_ID INNER JOIN
CRM_Orders ON CRM_SubjectReferences.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID INNER JOIN
CRM_CodeTexts ON CRM_SubjectReferences.KURZTEXT = CRM_CodeTexts.KURZTEXT INNER JOIN
CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_SubjectReferences.CODEGRUPPE = 'SRVCODES'
AND CRM_StatusCodes.USER_STATUS = 'Complete'
AND CRM_PartsLabor.DESCRIPTION IN ('FSE Labor','Call Center Labor')
AND CRM_SubjectReferences.kurztext like ('A04%')
AND CRM_Orders.priority = '2'
AND CRM_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')
GROUP BY
CRM_StatusCodes.TRANSACTION_ID
,CRM_StatusCodes.CREATED_AT
,CRM_StatusCodes.USER_STATUS
,CRM_Serials.SERIAL
,CRM_PartsLabor.DESCRIPTION
,CRM_Orders.PRIORITY_DESCRIPTION
,CRM_Orders.PRIORITY
,CRM_CodeTexts.ABCDE_CODE_DESC
,CRM_Partners.DESCRIPTION
,CRM_Partners.ADDRESS
And this is is my count statement I want in the above query:
SELECT
CRM_StatusCodes.transaction_ID, count(*) TotalCount
FROM
CRM_StatusCodes
GROUP BY CRM_StatusCodes.transaction_ID
HAVING count (*)>1
ORDER BY count (*) desc
The only way I could figure out how to do my count statement is by breaking it down. I know this is probably a very elementary question but I'm so terrible with nested queries and I tried inserting it in several different manners but I still can't get it to work out right. I'm thinking that I might need a CASE statement in there somewhere??!!
Thank you very much in advance for any assistance you can provide!!
Michelle :unsure:
May 6, 2009 at 12:52 pm
Like this:
SELECT
CRM_StatusCodes.TRANSACTION_ID
,CRM_StatusCodes.CREATED_AT
,CRM_StatusCodes.USER_STATUS, CRM_Serials.SERIAL
,CRM_PartsLabor.DESCRIPTION
,CRM_Orders.PRIORITY_DESCRIPTION
,CRM_Orders.PRIORITY
/*,CRM_StatusCodes.START_DATE*/
/*,CRM_StatusCodes.END_DATE*/
,CRM_CodeTexts.ABCDE_CODE_DESC
,CRM_Partners.DESCRIPTION AS [Function]
,CRM_Partners.ADDRESS
FROM
(
SELECT
CRM_StatusCodes.transaction_ID, count(*) TotalCount
FROM
CRM_StatusCodes
GROUP BY CRM_StatusCodes.transaction_ID
HAVING count (*)>1
) as CRM_StatusCodes INNER JOIN
CRM_Serials ON CRM_StatusCodes.ORDER_ID = CRM_Serials.ORDER_ID INNER JOIN
CRM_SubjectReferences ON CRM_StatusCodes.TRANSACTION_ID = CRM_SubjectReferences.TRANSACTION_ID INNER JOIN
CRM_PartsLabor ON CRM_SubjectReferences.TRANSACTION_ID = CRM_PartsLabor.TRANSACTION_ID INNER JOIN
CRM_Orders ON CRM_SubjectReferences.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID INNER JOIN
CRM_CodeTexts ON CRM_SubjectReferences.KURZTEXT = CRM_CodeTexts.KURZTEXT INNER JOIN
CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_SubjectReferences.CODEGRUPPE = 'SRVCODES'
AND CRM_StatusCodes.USER_STATUS = 'Complete'
AND CRM_PartsLabor.DESCRIPTION IN ('FSE Labor','Call Center Labor')
AND CRM_SubjectReferences.kurztext like ('A04%')
AND CRM_Orders.priority = '2'
AND CRM_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')
GROUP BY
CRM_StatusCodes.TRANSACTION_ID
,CRM_StatusCodes.CREATED_AT
,CRM_StatusCodes.USER_STATUS
,CRM_Serials.SERIAL
,CRM_PartsLabor.DESCRIPTION
,CRM_Orders.PRIORITY_DESCRIPTION
,CRM_Orders.PRIORITY
,CRM_CodeTexts.ABCDE_CODE_DESC
,CRM_Partners.DESCRIPTION
,CRM_Partners.ADDRESS
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 1:08 pm
Thank you RBarry,
That seemed so simple.
However, I'm getting this error now and my query isn't executing.
Msg 207, Level 16, State 1, Line 30
Invalid column name 'ORDER_ID'.
Msg 207, Level 16, State 1, Line 38
Invalid column name 'USER_STATUS'.
Msg 207, Level 16, State 1, Line 45
Invalid column name 'CREATED_AT'.
Msg 207, Level 16, State 1, Line 46
Invalid column name 'USER_STATUS'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'CREATED_AT'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'USER_STATUS'.
It's a bit strange why I'm getting this error now when they are indeed valid column names. But you gave me a direction to follow. I'll try working with it some more.
Michelle 🙂
May 6, 2009 at 1:23 pm
It's returning those errors because your subquery does not include all of those columns. You might try something like the following instead:
...
FROM CRM_StatusCodes INNER JOIN
...
WHERE CRM_StatusCodes.transaction_ID IN(
SELECT
CRM_StatusCodes.transaction_ID
FROM
CRM_StatusCodes
GROUP BY CRM_StatusCodes.transaction_ID
HAVING count (*)>1
)
...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 2:08 pm
Thank you for the explanation RBarry, it worked now.
But now, I'm realizing that I don't think that's what I wanted to do. :crying: Here's an example of my data.
TRANSACTION_IDSERIALCREATED_ATUSER_STATUSDESCRIPTION
300000090911005920090319234327Complete Labor
300000090911005920090319234327Complete Labor
300000090911005920090319234327Complete Labor
300000285211006720090416003349Complete Help Desk
300000285211006720090416003349Complete Help Desk
300000021211005920090316164224Complete Help Desk
300000021211005920090316164224Complete Labor
I have transaction_ID '3000000909' and Serial '110059' but then that serial number is repeated again but with a differrent transaction_ID, '3000000212'. I'd like a column that tells me how many times a serial number is repeated per transaction_ID.
So it would look something like this:
TRANSACTION_IDSERIALCREATED_ATUSER_STATUSDESCRIPTION TotalCount
300000090911005920090319234327CompleteLabor 2
300000090911005920090319234327CompleteLabor 2
300000090911005920090319234327CompleteLabor 2300000285211006720090416003349CompleteHelp Desk 1
300000285211006720090416003349CompleteHelp Desk 1
300000021211005920090316164224CompleteHelp Desk 2
300000021211005920090316164224CompleteLabor 2
I hope that makes sense and I want it in that query in my first post (with all the extra columns and where clauses). I'm not sure if this is the best way to report on this data so I'm open to ideas.
Thank you again for your help and time RBarry!!
With all the help I'm getting here on the forum I'll be able to someday contribute something worthy.
Michelle 😉
May 6, 2009 at 2:26 pm
OK, then replace this:
...
FROM CRM_StatusCodes INNER JOIN
...
with this:
...
FROM (
Select *
, (Select count(distinct transaction_ID)
From CRM_StatusCodes c2
Where c2.serial = c1.serial) as TotalCount
From CRM_StatusCodes c1
) as CRM_StatusCodes INNER JOIN
...
(not tested)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 2:57 pm
Wow, cool! Thanks. I thought the whole thing would have to be re-written.
I'm just getting this error now.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'serial'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'serial'.
I remember you said before that I was getting that error because I didn't have the column in my subquery.
And I looked at it and is it because I don't have a column called 'serial' in crm_statuscodes?
Thanks for your help, this is very elightening to a newbie like me!
Michelle
May 6, 2009 at 3:08 pm
Sorry, I mistakenly assumed that SERIAL was part of the CRM_StatusCodes. I'll have to give it another look...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 7:56 pm
OK, I think that this should work:
SELECT
CRM_StatusCodes.TRANSACTION_ID
,CRM_StatusCodes.CREATED_AT
,CRM_StatusCodes.USER_STATUS
, CRM_Serials.SERIAL
, CRM_Serials.TotalCount
,CRM_PartsLabor.DESCRIPTION
,CRM_Orders.PRIORITY_DESCRIPTION
,CRM_Orders.PRIORITY
/*,CRM_StatusCodes.START_DATE*/
/*,CRM_StatusCodes.END_DATE*/
,CRM_CodeTexts.ABCDE_CODE_DESC
,CRM_Partners.DESCRIPTION AS [Function]
,CRM_Partners.ADDRESS
FROM
CRM_StatusCodes INNER JOIN
( Select *
, (Select count(distinct transaction_ID)
From CRM_StatusCodes c2
Where c2.ORDER_ID = c1.ORDER_ID) as TotalCount
From CRM_Serials c1
) as CRM_Serials ON CRM_StatusCodes.ORDER_ID = CRM_Serials.ORDER_ID INNER JOIN
CRM_SubjectReferences ON CRM_StatusCodes.TRANSACTION_ID = CRM_SubjectReferences.TRANSACTION_ID INNER JOIN
CRM_PartsLabor ON CRM_SubjectReferences.TRANSACTION_ID = CRM_PartsLabor.TRANSACTION_ID INNER JOIN
CRM_Orders ON CRM_SubjectReferences.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID INNER JOIN
CRM_CodeTexts ON CRM_SubjectReferences.KURZTEXT = CRM_CodeTexts.KURZTEXT INNER JOIN
CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE CRM_StatusCodes.transaction_ID IN(
SELECT
CRM_StatusCodes.transaction_ID
FROM
CRM_StatusCodes
GROUP BY CRM_StatusCodes.transaction_ID
HAVING count (*)>1
)CRM_SubjectReferences.CODEGRUPPE = 'SRVCODES'
AND CRM_StatusCodes.USER_STATUS = 'Complete'
AND CRM_PartsLabor.DESCRIPTION IN ('FSE Labor','Call Center Labor')
AND CRM_SubjectReferences.kurztext like ('A04%')
AND CRM_Orders.priority = '2'
AND CRM_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')
GROUP BY
CRM_StatusCodes.TRANSACTION_ID
,CRM_StatusCodes.CREATED_AT
,CRM_StatusCodes.USER_STATUS
,CRM_Serials.SERIAL
,CRM_PartsLabor.DESCRIPTION
,CRM_Orders.PRIORITY_DESCRIPTION
,CRM_Orders.PRIORITY
,CRM_CodeTexts.ABCDE_CODE_DESC
,CRM_Partners.DESCRIPTION
,CRM_Partners.ADDRESS
I cannot really tell without table definitions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 9:47 am
Thanks, RBarry, I was able to get it to work...
Michelle 🙂
May 13, 2009 at 10:50 am
Great! Glad it worked for you then.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply