• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

count of query

I have a query that works perfectly, I would like to amend it so that it returns the count of itself ie if it returns 50 rows then return 50 etc. If I can do it this way it avoids creating another query.

I am not sure how to create the from with a select when referring to itself.

SELECT QpNewCodes.[mvris code], QryMatchDataParameterized.A.[Mvris Code], QryMatchDataParameterized.C.MvrisCode, QryMatchDataParameterized.G.[Mvris Code], QryMatchDataParameterized.V.MvrisCode, QryMatchDataParameterized.B.[Mvris Code], QryMatchDataParameterized.D.MvrisCode, QryMatchDataParameterized.H.MvrisCode, QryMatchDataParameterized.CWCode, QryMatchDataParameterized.T.[Mvris Code], QryMatchDataParameterized.T.[Mvris Code], QryMatchDataParameterized.[VEHICLE CATEGORY CODE], QryMatchDataParameterized.ISCWCode, QryMatchDataParameterized.Expr2
FROM QpNewCodes RIGHT JOIN QryMatchDataParameterized ON QpNewCodes.[mvris code] = QryMatchDataParameterized.N.[Mvris Code]
WHERE (((QpNewCodes.[mvris code]) Is Not Null));

Open in new window

0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 4
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Add a COUNT in the SELECT block, and GROUP BY all the other columns.
btw I also added aliases nc and mdp to make it easier to read.  And, if you lose the spaces in your column names you can remove the square brackets [ ] as well.


SELECT
      nc.[mvris code],
      nc.A.[Mvris Code],
      nc.C.MvrisCode,
      nc.G.[Mvris Code],
      nc.V.MvrisCode,
      nc.B.[Mvris Code],
      nc.D.MvrisCode,
      nc.H.MvrisCode,
      nc.CWCode,
      nc.T.[Mvris Code],
      nc.T.[Mvris Code],
      nc.[VEHICLE CATEGORY CODE],
      nc.ISCWCode,
      nc.Expr2,
      COUNT(*** any column goes here ***) as my_count
FROM QpNewCodes nc
      RIGHT JOIN QryMatchDataParameterized mdp ON nc.[mvris code] = mdp.N.[Mvris Code]
WHERE nc.[mvris code] Is Not Null
GROUP BY
      nc.[mvris code],
      nc.A.[Mvris Code],
      nc.C.MvrisCode,
      nc.G.[Mvris Code],
      nc.V.MvrisCode,
      nc.B.[Mvris Code],
      nc.D.MvrisCode,
      nc.H.MvrisCode,
      nc.CWCode,
      nc.T.[Mvris Code],
      nc.T.[Mvris Code],
      nc.[VEHICLE CATEGORY CODE],
      nc.ISCWCode,
      nc.Expr2
0
 
PeterBaileyUkAuthor Commented:
The query does work sort of, I should have said that the other two queries have parameters, its asking correctly for the parameters but then its asking again for the same parameter preceded by NC. I have attached screenshot.
ee.PNG
0
 
PeterBaileyUkAuthor Commented:
so one query is:

PARAMETERS [Yes=CW Codes/No=SMMT Codes] Bit, [Enter Cars, Bikes, Lcv, Others] Text ( 255 ), [Yes=Matched Abi/No=No Match Abi] Bit, [Yes=Matched Cap/No=No Match Cap] Bit, [Yes=Matched Glass/No=No Match Glass] Bit, [Yes=Matched Adl/No=No Match Adl] Bit, [Yes=Matched Insecom/No=No Match Insecom] Bit, [Yes=Matched TechDoc/No=No Match TechDoc] Bit, [Yes=Matched Halfords/No=No Match Halfords] Bit, [Yes=Matched Kee/No=No Match Kee] Bit, [Yes=Matched Vivid/No=No Match Vivid] Bit;
SELECT N.[MVRIS CODE], A.[MVRIS CODE], C.MvrisCode, G.[MVRIS CODE], V.MvrisCode, B.[MVRIS CODE], D.MvrisCode, H.MvrisCode, K.CWCode, T.[Mvris Code], S.[VEHICLE CATEGORY CODE], N.[MVRIS CODE] Like "??[89140]*" AS ISCWCode, IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))) AS Expr2
FROM (((((((((QryGetNewCodes AS N LEFT JOIN QryAbiMatch AS A ON N.[MVRIS CODE] = A.[MVRIS CODE]) LEFT JOIN QryCapMatch AS C ON N.[MVRIS CODE] = C.MvrisCode) LEFT JOIN QryGlassMatch AS G ON N.[MVRIS CODE] = G.[MVRIS CODE]) LEFT JOIN QryVividMatch AS V ON N.[MVRIS CODE] = V.MvrisCode) LEFT JOIN SMMT AS S ON N.[MVRIS CODE] = S.[MVRIS CODE]) LEFT JOIN QryAbiMatchInsecom AS B ON N.[MVRIS CODE] = B.[MVRIS CODE]) LEFT JOIN QryADLMatch AS D ON N.[MVRIS CODE] = D.MvrisCode) LEFT JOIN QryHalfordsMatch AS H ON N.[MVRIS CODE] = H.MvrisCode) LEFT JOIN QryKeeMatch AS K ON N.[MVRIS CODE] = K.CWCode) LEFT JOIN QryTechDocMatch AS T ON N.[MVRIS CODE] = T.[Mvris Code]
WHERE (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([A].[MVRIS CODE] Is Null)<>[Yes=Matched Abi/No=No Match Abi])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([C].[MvrisCode] Is Null)<>[Yes=Matched Cap/No=No Match Cap])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([G].[MVRIS CODE] Is Null)<>[Yes=Matched Glass/No=No Match Glass])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([D].[MVRISCODE] Is Null)<>[Yes=Matched Adl/No=No Match Adl])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([B].[MVRIS CODE] Is Null)<>[Yes=Matched Insecom/No=No Match Insecom])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([T].[MVRIS CODE] Is Null)<>[Yes=Matched TechDoc/No=No Match TechDoc])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([H].[MVRISCODE] Is Null)<>[Yes=Matched Halfords/No=No Match Halfords])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([K].[CWCODE] Is Null)<>[Yes=Matched Kee/No=No Match Kee])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([V].[MVRISCODE] Is Null)<>[Yes=Matched Vivid/No=No Match Vivid]));

Open in new window



second query is :

PARAMETERS BYM DateTime;
SELECT L.[mvris code]
FROM (SELECT [mvris code] FROM CWSMMTBuilds WHERE BuildYearMonth=Format(BYM,'yyyy-mm'))  AS L LEFT JOIN (SELECT [mvris code] FROM CWSMMTBuilds WHERE BuildYearMonth=Format(DateAdd('m',-1,BYM),'yyyy-mm'))  AS P ON L.[mvris code] = P.[mvris code]
WHERE (((P.[mvris code]) Is Null));

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
hnasrCommented:
try to:
Reduce the query to 2 or 3 fields.
List few records of the query result.
List the expected output.
0
 
PeterBaileyUkAuthor Commented:
ok rather than taking two queries I have created one query that works and I am back to the same I need the total of itself:

I have aliased it but I cannot remove anything else. Again I am not sure how to change it so I get the total.


PARAMETERS [Yes=CW Codes/No=SMMT Codes] Bit, [Enter Cars, Bikes, Lcv, Others] Text ( 255 ), [Yes=Matched Abi/No=No Match Abi] Bit, [Yes=Matched Cap/No=No Match Cap] Bit, [Yes=Matched Glass/No=No Match Glass] Bit, [Yes=Matched Adl/No=No Match Adl] Bit, [Yes=Matched Insecom/No=No Match Insecom] Bit, [Yes=Matched TechDoc/No=No Match TechDoc] Bit, [Yes=Matched Halfords/No=No Match Halfords] Bit, [Yes=Matched Kee/No=No Match Kee] Bit, [Yes=Matched Vivid/No=No Match Vivid] Bit;
SELECT N.[MVRIS CODE], A.[MVRIS CODE], C.MvrisCode, G.[MVRIS CODE], V.MvrisCode, B.[MVRIS CODE], D.MvrisCode, H.MvrisCode, K.CWCode, T.[Mvris Code], S.[VEHICLE CATEGORY CODE], N.[MVRIS CODE] Like "??[89140]*" AS ISCWCode, IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))) AS Expr2
FROM (((((((((QpNewCodes AS N LEFT JOIN QryAbiMatch AS A ON N.[MVRIS CODE] = A.[MVRIS CODE]) LEFT JOIN QryCapMatch AS C ON N.[MVRIS CODE] = C.MvrisCode) LEFT JOIN QryGlassMatch AS G ON N.[MVRIS CODE] = G.[MVRIS CODE]) LEFT JOIN QryVividMatch AS V ON N.[MVRIS CODE] = V.MvrisCode) LEFT JOIN SMMT AS S ON N.[MVRIS CODE] = S.[MVRIS CODE]) LEFT JOIN QryAbiMatchInsecom AS B ON N.[MVRIS CODE] = B.[MVRIS CODE]) LEFT JOIN QryADLMatch AS D ON N.[MVRIS CODE] = D.MvrisCode) LEFT JOIN QryHalfordsMatch AS H ON N.[MVRIS CODE] = H.MvrisCode) LEFT JOIN QryKeeMatch AS K ON N.[MVRIS CODE] = K.CWCode) LEFT JOIN QryTechDocMatch AS T ON N.[MVRIS CODE] = T.[Mvris Code]
WHERE (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([A].[MVRIS CODE] Is Null)<>[Yes=Matched Abi/No=No Match Abi])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([C].[MvrisCode] Is Null)<>[Yes=Matched Cap/No=No Match Cap])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([G].[MVRIS CODE] Is Null)<>[Yes=Matched Glass/No=No Match Glass])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([D].[MVRISCODE] Is Null)<>[Yes=Matched Adl/No=No Match Adl])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([B].[MVRIS CODE] Is Null)<>[Yes=Matched Insecom/No=No Match Insecom])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([T].[MVRIS CODE] Is Null)<>[Yes=Matched TechDoc/No=No Match TechDoc])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([H].[MVRISCODE] Is Null)<>[Yes=Matched Halfords/No=No Match Halfords])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([K].[CWCODE] Is Null)<>[Yes=Matched Kee/No=No Match Kee])) OR (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([V].[MVRISCODE] Is Null)<>[Yes=Matched Vivid/No=No Match Vivid]));

Open in new window

0
 
hnasrCommented:
If you have time, have a look at my comment http:#38283837
Being the author, you should be able to remove fields from query and simplify it, so we concentrate on the problem "total"
Few records and respective output help in better understanding the requirement.
0
 
PeterBaileyUkAuthor Commented:
ok I have removed all bar one of the select fields from the clients so I have only one client in here. I wasnt sure if i should remove the joins to the client tables, i left them in as if i remove.

the query will be simple yes but i wont know how to build it back up.

But i think by your reckoning its still too complex.

PARAMETERS [Yes=CW Codes/No=SMMT Codes] Bit, [Enter Cars, Bikes, Lcv, Others] Text ( 255 ), [Yes=Matched Abi/No=No Match Abi] Bit;
SELECT N.[MVRIS CODE], A.[MVRIS CODE], S.[VEHICLE CATEGORY CODE], N.[MVRIS CODE] Like "??[89140]*" AS ISCWCode, IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))) AS Expr2
FROM (((((((((QpNewCodes AS N LEFT JOIN QryAbiMatch AS A ON N.[MVRIS CODE] = A.[MVRIS CODE]) LEFT JOIN QryCapMatch AS C ON N.[MVRIS CODE] = C.MvrisCode) LEFT JOIN QryGlassMatch AS G ON N.[MVRIS CODE] = G.[MVRIS CODE]) LEFT JOIN QryVividMatch AS V ON N.[MVRIS CODE] = V.MvrisCode) LEFT JOIN SMMT AS S ON N.[MVRIS CODE] = S.[MVRIS CODE]) LEFT JOIN QryAbiMatchInsecom AS B ON N.[MVRIS CODE] = B.[MVRIS CODE]) LEFT JOIN QryADLMatch AS D ON N.[MVRIS CODE] = D.MvrisCode) LEFT JOIN QryHalfordsMatch AS H ON N.[MVRIS CODE] = H.MvrisCode) LEFT JOIN QryKeeMatch AS K ON N.[MVRIS CODE] = K.CWCode) LEFT JOIN QryTechDocMatch AS T ON N.[MVRIS CODE] = T.[Mvris Code]
WHERE (((IIf([vehicle category code]<'c',"cars",IIf([vehicle category code]>'t',"Bikes",IIf([vehicle category code]>'c' And [vehicle category code]<'e',"LCV","Others"))))=[Enter Cars, Bikes, Lcv, Others]) AND (([N].[MVRIS CODE] Like "??[8914O]*")=[Yes=CW Codes/No=SMMT Codes]) AND (([A].[MVRIS CODE] Is Null)<>[Yes=Matched Abi/No=No Match Abi]));

Open in new window

0
 
hnasrCommented:
"But i think by your reckoning its still too complex."
Sure it is, because of criteria which is your playground.

"the query will be simple yes but i wont know how to build it back up."
be simple: that's fine. So I understand and try to help. If you can solve it when simple, then you may add your solution and experts will add to it.
build back: Understanding a simple case, one can expand to more complex query.

Don't forget to add few test records and the anticipated output.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now