View Full Version : SQL help, ms access
lienlee
05-31-2010, 07:53 AM
SELECT count (distinct Subject_Area.Subject_Area_Name) as "number", Subject_Domain.Subject_Domain_Name
FROM Data_Model, (Subject_Domain INNER JOIN Subject_Area ON Subject_Domain.Subject_Domain_ID = Subject_Area.Subject_Domain_ID) INNER JOIN Conceptual_Entity ON Subject_Area.Subject_Area_ID = Conceptual_Entity.Subject_Area_ID
it keeps saying i have a syntax error(missing operator) in query expression 'count(distinct subject_area.subject_Area_name)
im trying to count each distinct subject area name for each subject domain name
Gollem
06-01-2010, 04:56 AM
Hi,
first remark. If you use a count, sum, avg or other function you must have a group by part in your sql-statement.
I'm familiar with SQL but your sql-statement doesn't make sence. I don't understand your statement.
You have tables: data_Model, Subject_Domain, Subject_Area, ...
Perhaps it's better to give the tables-structure first. How many tables are you trying to join?
Globally I think you just need a group by and a count, the distinct won't be necessary.
lienlee
06-02-2010, 08:10 AM
Hi,
first remark. If you use a count, sum, avg or other function you must have a group by part in your sql-statement.
I'm familiar with SQL but your sql-statement doesn't make sence. I don't understand your statement.
You have tables: data_Model, Subject_Domain, Subject_Area, ...
Perhaps it's better to give the tables-structure first. How many tables are you trying to join?
Globally I think you just need a group by and a count, the distinct won't be necessary.
SELECT Subject_Domain.Subject_Domain_Name, Subject_Area.Subject_Area_Name, Count(Distinct [Subject_Area.Subject_Area_Name]) AS CountOfSubject_Area_Name
FROM Data_Model, Subject_Domain INNER JOIN (Subject_Area INNER JOIN Conceptual_Entity ON Subject_Area.Subject_Area_ID = Conceptual_Entity.Subject_Area_ID) ON Subject_Domain.Subject_Domain_ID = Subject_Area.Subject_Domain_ID
GROUP BY Subject_Domain.Subject_Domain_Name, Subject_Area.Subject_Area_Name
ORDER BY Subject_Domain.Subject_Domain_Name DESC;
im looking for the count of distinct subject area names.
im inner joining subject domain subject area and conceptual entity
data model is not inner joined to anything
when i count it counts everything. I just wish to count distinct names only.
Gollem
06-02-2010, 11:25 PM
Hi,
I understand what you are trying to do, I did some research and testing.
I've learned something, your combination distinct and count on a field doesn't seem to work in Access but in an sql-server database it seems to work.
I've learned something.
I've said that with a count you always need a group by ... this is wrong apparently. If you only want to count some records using 1 field it's no problem. ex.
select count(Name)
from table where age = 29
In your case, your first statement would work in an sql-server like this:
SELECT count (distinct Subject_Area.Subject_Area_Name) as number,
FROM Data_Model, (Subject_Domain INNER JOIN Subject_Area ON Subject_Domain.Subject_Domain_ID = Subject_Area.Subject_Domain
or with the domain_name=> number of distinct areas for each domain:
SELECT count (distinct Subject_Area.Subject_Area_Name) as number, Subject_Domain.Subject_Domain_Name
FROM Data_Model, (Subject_Domain INNER JOIN Subject_Area ON Subject_Domain.Subject_Domain_ID = Subject_Area.Subject_Domain
group by Subject_Domain.Subject_Domain_Name
Are you using access?
If this feedback helps to solve the issue, let me know the outcome. Interesting case.
Gollem
06-02-2010, 11:30 PM
Hi,
I found a solution for access. But I'm not sure if it will solve your problem.
You can use an extra query. 1 query : select distinct .....
Then in your new query select count(*) from query
See this thread: http://www.keyongtech.com/83635-sql-is-select-count-distinct
lienlee
06-04-2010, 08:34 AM
Hi,
I found a solution for access. But I'm not sure if it will solve your problem.
You can use an extra query. 1 query : select distinct .....
Then in your new query select count(*) from query
See this thread: http://www.keyongtech.com/83635-sql-is-select-count-distinct
Hi,
This way only counts the number of distinct items. It doesnt count the number of each distinct items.
For example
Business can have 4 marketing teams, 3 accounting teams
itll only count that theres 2 different types of teams. not how many marketing eachs and accounting teams.
I've solved my problem though
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.