We help IT Professionals succeed at work.

Need help with tricky SELECT

petekipe
petekipe asked
on
I need to return the count of remaining items among rows of the same type.  Here's a starting SELECT statement:

SELECT InspectionType.inspection_type_name, 
              [last_sticker_no] - [next_sticker_no] + 1 AS remaining_stickers, 
              InspectionStickers.status
FROM  (InspectionStickers 
INNER JOIN CampaignYear ON InspectionStickers.fk_campaign_year = CampaignYear.pk_campaign_year) 
INNER JOIN InspectionType ON CampaignYear.fk_inspection_type = InspectionType.pk_inspection_type
WHERE InspectionStickers.status='C'
    AND  InspectionType.pk_inspection_type = 1
    AND  CampaignYear.pk_campaign_year = 4

Open in new window


The statement returns the number of unused sticker numbers in each row having a status of 'C' for inspection type 1 by campaign year (4 in this example).

I need to add to that count the number of unused sticker numbers in other rows having a status of 'N' with the same inspection type and campaign year.  I'm sure I need a sub SELECT, but I don't know how to structure it.  Any help would be greatly appreciated!  Pete
Comment
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this -
SELECT InspectionType.inspection_type_name, 
              --[last_sticker_no] - [next_sticker_no] + 1 AS remaining_stickers, 
              --InspectionStickers.status
			   SUM ( CASE WHEN InspectionStickers.status='C' THEN 1 ELSE 0 END )  UnusedStickersC
			  ,SUM ( CASE WHEN InspectionStickers.status='N' THEN 1 ELSE 0 END )  UnusedStickersN
FROM  (InspectionStickers 
INNER JOIN CampaignYear ON InspectionStickers.fk_campaign_year = CampaignYear.pk_campaign_year) 
INNER JOIN InspectionType ON CampaignYear.fk_inspection_type = InspectionType.pk_inspection_type
WHERE 
    AND  InspectionType.pk_inspection_type = 1
    AND  CampaignYear.pk_campaign_year = 4
GROUP BY InspectionType.inspection_type_name

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
If you face any issues then pls provide some sample data and expected output.
petekipeOwner

Author

Commented:
Thanks for the quick response, but I don't think I made my request clear.  Attached are the three tables with sample data:InspectionType, CampaignYear and InspectionStickers tables - MS Access database
I'm looking for a result set that would look like this:

Annual Inside                 247
Semi-Annual Inside       10
Annual Outside              4
Emissions                        150

For this query, rows in status 'U' are ignored.  All but the Annual Inside type involve only a single InspectionStickers status 'C' row, but there are two Annual Inside rows - one in status 'C' (pk 5) and the other status 'N' (pk 7).  I need to be able to sum the total of all unused stickers (to_sticker_no - next_sticker_no + 1) in both rows.  And I want the result set to look like the four rows shown above.  There will only be one row in status 'C' for any inspection type and campaign year, but there can be more than one row is status 'N' for a given inspection type and campaign year.

Pete
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
and here is the query:
transcribe this if you really want to
 Images of data are painful, just like images of solutions would be.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
sorry (silly mood); but is there any chance you can just copy/paste some data into code boxes? or, for access, you can upload a db file of just the stuff you care to share.
petekipeOwner

Author

Commented:
Sorry - attached is a stripped-down Access 2000 database containing the three tables and the beginnings of a query.

PeteSample.mdb
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Hey,

Can you explain me the how you are getting

247 for Annual Inside  ??
petekipeOwner

Author

Commented:
InspectionStickers primary key 5 shows next_sticker_no of 1253804 and to_sticker_no of 1253950, so 1253950 - 1253804  + 1 = 147.
InspectionStickers primary key 7 shows next_sticker_no of 2590481 and to_sticker_no of 2590580, so 2590580 - 2590481  + 1 = 100.
The total of the two = 247.

If 2 stickers of 10 have been used, the next sticker would be 3 - and to calculate the number remaining (which should be 8), we subtract 3 from 10 and add 1.

Pete
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Thank you. Will provide you a query in some time. :)
petekipeOwner

Author

Commented:
Just to clarify, InspectionStickers primary keys 5 and 7 have fk_inspection_type and fk_campaign_year in common (1 and 4), and neither is in status 'U', so the remaining counts need to be combined.

Pete
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Okies. Got it.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this solution -

SELECT Inspection_type_name , SUM( to_sticker_no - next_sticker_no + 1 ) as SummationStickers FROM 
inspectionStickers as iss
INNER JOIN CampaignYear as cy ON iss.fk_CampaignYear = cy.pk_CampaignYear AND iss.fk_Inspection_Type = cy.fk_Inspection_Type
INNER JOIN InspectionType it ON it.pk_Inspection_Type = iss.fk_Inspection_Type
WHERE [Status] <> 'U'
GROUP BY Inspection_type_name

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
Inspection_type_name                               SummationStickers
-------------------------------------------------- --------------------
Annual Inside                                      247
Annual Outside                                     4
Emissions                                          150
Semi-Annual Inside                                 10

(4 row(s) affected)

Open in new window

petekipeOwner

Author

Commented:
Sweet approach - I never thought of it.  But...

I copied your solution into the sample database I attached earlier and ran it - got the error 'Syntax error (missing operator) in query expression ''.  I'm running Office 365 installed locally, version 1709 (build 8528.2147).
petekipeOwner

Author

Commented:
I don't think it likes the AND in the first join
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Updated..Pls try(as was missing)

SELECT Inspection_type_name , SUM ( to_sticker_no - next_sticker_no + 1 ) as SummationStickers FROM
inspectionStickers as iss
INNER JOIN CampaignYear as cy ON ( iss.fk_CampaignYear = cy.pk_CampaignYear ) AND ( iss.fk_Inspection_Type = cy.fk_Inspection_Type )
INNER JOIN InspectionType as it ON ( it.pk_Inspection_Type = iss.fk_Inspection_Type )
WHERE Status <> 'U'
GROUP BY Inspection_type_name
petekipeOwner

Author

Commented:
You had a couple of field names incorrectly spelled, so I fixed them.  But, I'm still getting 'Syntax error (missing operator) in query expression '( iss.fk_Campaign...etc.  

I tried building the query using Access' Query Design, but Access wants me to group on status, and that doesn't allow 'C' and 'N' rows to be combined.  Here's Access' code:

SELECT InspectionType.inspection_type_name, 
       Sum([to_sticker_no]-[next_sticker_no]+1) AS SummationStickers
FROM (InspectionStickers 
INNER JOIN CampaignYear ON InspectionStickers.fk_campaign_year = CampaignYear.pk_campaign_year) 
INNER JOIN InspectionType ON CampaignYear.fk_inspection_type = InspectionType.pk_inspection_type
GROUP BY InspectionType.inspection_type_name, InspectionStickers.status
HAVING (((InspectionStickers.status)<>'U'));

Open in new window


It runs, but I'm not getting the result I want.  'C' and 'N' rows are returned separately.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Do not build the query...

You can directly go to the query window where you can run the sql query.

This will help you to go to SQL query window - > http://www.jaffainc.com/SQLStatementsInAccess.htm
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Do not group on InspectionStickers.status

SELECT InspectionType.inspection_type_name,
       Sum([to_sticker_no]-[next_sticker_no]+1) AS SummationStickers
FROM (InspectionStickers
INNER JOIN CampaignYear ON InspectionStickers.fk_campaign_year = CampaignYear.pk_campaign_year)
INNER JOIN InspectionType ON CampaignYear.fk_inspection_type = InspectionType.pk_inspection_type
WHERE (((InspectionStickers.status)<>'U'))
GROUP BY InspectionType.inspection_type_name
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Edited my last comment.
petekipeOwner

Author

Commented:
That did it!  Many thanks for your help in getting this working for me - I really appreciate it.