PDA

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