SQL: query to show how many users have each proper

问题:

I'm new to SQL and can't seem to ask the right question, as in every time I formulate it differently I end up with the same results: A query that groups data by several properties (users that share more than one property).

I'm writing a JSP-based website on the Glassfish server, and manage the database from MS Access.

What I'm interested in, is basically grouping by several distinct properties. Say I have a table that shows which items a user has. Something like this:

id  |  name |  item1  | item2  | item3  | item4  |
--------------------------------------------------
1   | name1 |   yes   |  yes   |        | yes    |
----+-------+---------+--------+--------+--------+
2   | name2 |   yes   |        |        | yes    |
----+-------+---------+--------+--------+--------+
3   | name3 |         |  yes   |        | yes    |
----+-------+---------+--------+--------+--------+
4   | name4 |   yes   |  yes   |        | yes    |
----+-------+---------+--------+--------+--------+
5   | name5 |         |        |  yes   | yes    | 
.
.
.

The query that I need, would return the following:

ItemID  |  Number of users with this item
-----------------------------------------
item1   |  3
item2   |  3
item3   |  1
item4   |  5

I don't see how GROUP BY can be used here, as the result I'm looking for basically demands that the properties of the original table, will now appear as the values in each row of the resulting table.

What is the right query, and how such operation is called (it's not grouping by each property, it's something else...)?

回答1:

I can't see how you could do this in Access if you need it to work on any table, but if you know the table structure you can design a query like:

select 'item1' as ItemID, count(*) as [Number of users with this item]
from myTable
where item1 = 'Yes'
union
select 'item2', count(*)
from myTable
where item2 = 'Yes'
union
....

and so on.

回答2:

You could do an aggregate query that returns 1 record with the count under each Item field. Assuming these are Yes/No type fields:

SELECT Sum(IIf([Item1],1,0)) AS Count1, Sum(IIf([Item2],1,0)) AS Count2, Sum(IIf([Item3],1,0)) AS Count3, Sum(IIf([Item4],1,0)) AS Count4 FROM Table1;

Your data structure is not normalized. Instead of 4 Item fields should be one Item field with the item code. Normalized structure would allow option for GROUP BY or CROSSTAB queries.

id  |  name |  item   |
----------------------+
1   | name1 |   1     |
----+-------+---------+
2   | name1 |   2     |
----+-------+---------+
3   | name1 |   4     |
----+-------+---------+
4   | name2 |   1     |
----+-------+---------+
5   | name2 |   4     |

Can emulate the normalized structure with a UNION query. There is no designer for UNION, must type into SQLView of query builder.

SELECT ID AS SourceRecID, [Name] AS User, 1 AS Item, "Item1" AS Source FROM tablename
UNION SELECT ID, [Name], 2, "Item2" FROM tablename
...;

Now use that query in another query to do the aggregate GROUP BY calcs. Or use it as the source for a report. A report will allow display of raw detail data as well as summary calcs.