in Education by
I have an access table that has in one column a list of groups and then another column that has a list of products. I would like to run a report to show how many groups are in all products, how many groups are at least 1 of the products. I have as many as 6 products in a given table and over 5,000 unique groups. Below is an example of what I am looking for: Table: Group | Product AAAA | 123456 AAAA | 234578 AAAA | 456789 AAAA | 789012 BBBB | 123456 BBBB | 234578 BBBB | 456789 CCCC | 123456 CCCC | 234578 CCCC | 456789 CCCC | 789012 Expected Results: Product 123456 and 234578 and 456789 and 789012 has 2 groups Product 123456 or 234578 or 456789 or 789012 or has 3 groups I tried the below query but it is only for when it is a product or another product not when in both. I am looking for when a group is in product x and product y and product z but not in product a. SELECT group FROM GroupProducts WHERE product in (’123456’,’2345678’,’456789’) and product not in (’789012’) JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by
I would like to run a report to show how many groups are in all products Assuming no duplicate records, the following query should return the set of all groups which are associated with all products in the dataset: select t.group from YourTable t group by t.group having count(t.group) = ( select count(*) from (select distinct u.product from YourTable u) ) You can count these groups by simply enclosing the above with a select count(*) query: select count(*) from ( select t.group from YourTable t group by t.group having count(t.group) = ( select count(*) from (select distinct u.product from YourTable u) ) ) how many groups are at least 1 of the products This one is significantly easier, since you can simply select the number of distinct groups in the dataset: select distinct t.group from YourTable t Which can then be counted in the same manner as previously described: select count(*) from ( select distinct t.group from YourTable t ) In all of the above examples, replace all instances of YourTable with the name of your table.

Related questions

0 votes
    I have an access table that has in one column a list of groups and then another column that has a ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked May 13, 2022 in Education by JackTerrance
0 votes
    Just wondering if any of you guys use Count(1) over Count(*) and if there is a noticeable difference in ... SQL Server 2005.) Select the correct answer from above options...
asked Jan 28, 2022 in Education by JackTerrance
0 votes
    i want to delete the wullfi row by match the authid i must also say the aid is change so cant ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 7, 2022 in Education by JackTerrance
0 votes
    How can I request a random row (or as close to truly random as is possible) in pure SQL? Select the correct answer from above options...
asked Feb 8, 2022 in Education by JackTerrance
0 votes
    How can I request a random row (or as close to truly random as is possible) in pure SQL? Select the correct answer from above options...
asked Feb 5, 2022 in Education by JackTerrance
0 votes
    I have an integer field person_id with the default value of NULL. I am using a stored procedure and ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 25, 2022 in Education by JackTerrance
0 votes
    I have seen SQL that uses both != and for not equal. What is the preferred syntax and why? I like !=, ... reminds me of Visual Basic. Select the correct answer from above options...
asked Jan 28, 2022 in Education by JackTerrance
0 votes
    I have a virtual machine (VMware) with Mercury Quick Test Professional 9.2 installed. I have a script ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 17, 2022 in Education by JackTerrance
0 votes
    This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 17, 2022 in Education by JackTerrance
0 votes
    The ___________ operation, denoted by −, allows us to find tuples that are in one relation but are not ... , Database Interview Questions and Answers for Freshers and Experience...
asked Oct 11, 2021 in Education by JackTerrance
0 votes
    I'm trying to get the number of rows of dataframe df with Pandas, and here is my code. Method 1: total_rows ... What am I doing wrong? Select the correct answer from above options...
asked Jan 27, 2022 in Education by JackTerrance
0 votes
    I would like to ask for performance of SQL. This is my query: Select A.code ,A.name ,(Select B. ... , JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jul 20, 2022 in Education by JackTerrance
0 votes
    I have a table in Db2 called myTable. It has several columns: a | b | date1 | date2 ----- ... , JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jun 14, 2022 in Education by JackTerrance
0 votes
    I have a table in Db2 called myTable. It has several columns: a | b | date1 | date2 ----- ... , JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jun 14, 2022 in Education by JackTerrance
0 votes
    I have a table in Db2 called myTable. It has several columns: a | b | date1 | date2 ----- ... , JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jun 10, 2022 in Education by JackTerrance
...