Sql count

[Eric2013]

is there such a thing as SQL COUNT(DISTINCT column_name) or some form of this that would look at more then one column?

been looking at w3schools and can't get what i'm looking for . Using access 2013. no sql servers.....yet...



tks in advance. Eric

[ranman256]

No sql counts what is in A column.
Youd have to count all columns then sum across
or
make a crosstab to sum it.

[NTC]

well adding to R256's input; sort of depends on how one interprets your post... yes for sure there is a Distinct query. And distinct is the entire record which can be multiple columns/fields. But the fact that you can't find what you are looking for makes me think I am not interpreting your post correctly.

[orange]

eric,

I agree with NTC re uncertain of your requirement. Can you post a few records and an example of what you want the query to return?

[Eric2013]

this is what i've tried to use as well as a few combinations.

I have a few columns of data with over 3000 work order ID's. What i'd like to be able to do, is for each unique part #, find out how many times each part # was used over a period of time over the entire table. EG: Part AF25555 might have been used a total of 23 times per 12 month rolling, but won't be in the same column or same work order ID.

I've tried this knowing it's just asking per row and a sub total and it works, if i want to total the columns..:

SELECT DISTINCTROW Sum([To Do List].[Other]) AS [Sum Of Other], Sum([To Do List].[Other 1]) AS [Sum Of Other 1], Sum([To Do List].[Other 2]) AS [Sum Of Other 2], Sum([To Do List].[Other 3]) AS [Sum Of Other 3], Sum([To Do List].[Other 4]) AS [Sum Of Other 4], Sum([To Do List].[Other 5]) AS [Sum Of Other 5]
FROM [To Do List];

but, i really want it to count how many times each part# was used.

tks again in advance for any help.

Eric

[JoeM]

What is making this so difficult is that it sounds like you have a database design issue, specifically your data is not normalized. You should not have 6 different columns holding these part numbers like (Other, Other 1, ..., Other 6). Rather, you should just have two columns, the first which holds the index, and the second the part number.

So instead of having something like 3000 records, each which 6 different fields for part numbers, you have should up to 18000 records, with exactly one part number in each record (in a single part number field). Then doing tasks like these is much easier.

See here for tips on Database Design and Concepts of Normalization: https://support.office.com/en-us/art...rs=en-US&ad=US