SQL assistance please
Discussion
I have a table called HOL_DETAILS and I am trying to count the number of times USA appears in the DESTINATION column.
I'm using this:
SELECT COUNT(*)
FROM "HOL_DETAILS"."DESTINATION"
WHERE "HOL_DETAILS"."DESTINATION" = USA;
I just get "table or view does not exist"... where have I gone wrong?
I'm using this:
SELECT COUNT(*)
FROM "HOL_DETAILS"."DESTINATION"
WHERE "HOL_DETAILS"."DESTINATION" = USA;
I just get "table or view does not exist"... where have I gone wrong?
justlivyalife said:
I have a table called HOL_DETAILS and I am trying to count the number of times USA appears in the DESTINATION column.
I'm using this:
SELECT COUNT(*)
FROM "HOL_DETAILS"."DESTINATION"
WHERE "HOL_DETAILS"."DESTINATION" = USA;
I just get "table or view does not exist"... where have I gone wrong?
I'm guessing:I'm using this:
SELECT COUNT(*)
FROM "HOL_DETAILS"."DESTINATION"
WHERE "HOL_DETAILS"."DESTINATION" = USA;
I just get "table or view does not exist"... where have I gone wrong?
SELECT COUNT(*)
FROM "HOL_DETAILS"
WHERE "HOL_DETAILS"."DESTINATION" = USA;
You select from a table, not a column.
Personally I'd write it as
SELECT COUNT(*)
FROM HOL_DETAILS
WHERE DESTINATION = 'USA'
ie don't quote the table & field names, but do quote the text string to search for. mySQL can also be a pig with explicit tablename when referencing columns, depending on the version. ie HOL_DETAILS.DESTINATION won't work, you have to alias the table first :
FROM HOL_DETAILS H
WHERE H.DESTINATION....
etc. I think that's been sorted now though and I know the OP didn't mention which flavour of SQL
SELECT COUNT(*)
FROM HOL_DETAILS
WHERE DESTINATION = 'USA'
ie don't quote the table & field names, but do quote the text string to search for. mySQL can also be a pig with explicit tablename when referencing columns, depending on the version. ie HOL_DETAILS.DESTINATION won't work, you have to alias the table first :
FROM HOL_DETAILS H
WHERE H.DESTINATION....
etc. I think that's been sorted now though and I know the OP didn't mention which flavour of SQL
Edited by Famous Graham on Wednesday 9th January 13:45
Famous Graham said:
Personally I'd write it as
SELECT COUNT(*)
FROM HOL_DETAILS
WHERE DESTINATION = 'USA'
Thanks for ALL the quick responses - I didn't realise it needed to be in single quote's - sorted now thanks!SELECT COUNT(*)
FROM HOL_DETAILS
WHERE DESTINATION = 'USA'
Any ideas how to do a group by query on the same column - need to count for each destination (Spain, France, Canada, USA)?
justlivyalife said:
Famous Graham said:
Personally I'd write it as
SELECT COUNT(*)
FROM HOL_DETAILS
WHERE DESTINATION = 'USA'
Thanks for ALL the quick responses - I didn't realise it needed to be in single quote's - sorted now thanks!SELECT COUNT(*)
FROM HOL_DETAILS
WHERE DESTINATION = 'USA'
ie
WHERE NAME = 'FRED'
WHERE NAME LIKE '%RED'
WHERE ID = 12
(assuming of course the ID field has been created as an integer field in the first place)
justlivyalife said:
Any ideas how to do a group by query on the same column - need to count for each destination (Spain, France, Canada, USA)?
SELECT DESTINATION, COUNT(*)FROM HOL_DESTINATIONS
GROUP BY DESTINATION
edit - too slow
Edited by Famous Graham on Wednesday 9th January 13:58
Thanks again guys...
I'm also trying to do an outer join between hol_details & difficulty_rating
each has a field called difficulty, and difficulty_rating has a description column which details which each code rating means. How do I sort the data so that it lists the descriptions, and then each holiday that applies to those descriptions?
I'm also trying to do an outer join between hol_details & difficulty_rating
each has a field called difficulty, and difficulty_rating has a description column which details which each code rating means. How do I sort the data so that it lists the descriptions, and then each holiday that applies to those descriptions?
justlivyalife said:
Thanks again guys...
I'm also trying to do an outer join between hol_details & difficulty_rating
each has a field called difficulty, and difficulty_rating has a description column which details which each code rating means. How do I sort the data so that it lists the descriptions, and then each holiday that applies to those descriptions?
I'm also trying to do an outer join between hol_details & difficulty_rating
each has a field called difficulty, and difficulty_rating has a description column which details which each code rating means. How do I sort the data so that it lists the descriptions, and then each holiday that applies to those descriptions?
select hd.*, dr.description_field from hol_details hd
inner join difficulty_rating dr
on hd.difficulty = dr.difficulty
order by dr.difficulty
This will list each holiday, and what difficulty_rating it belongs to, I think...assuming I've got the names right
Can anyone tell me why this:
SELECT HOL_DETAILS.HOL_CODE, HOL_DETAILS.DESCRIPTION, HOL_DETAILS.DURATION, SITE_DETAILS.SITE_NAME
FROM HOL_DETAILS, SITES_VISTED, SITE_DETAILS
WHERE SITES_VISITED.SITE_CODE = 101
ORDER BY HOL_DETAILS.HOL_CODE;
Is simply returning a list of every single HOL_CODE combined with every single SITE_CODE?
It should only show the HOL_CODEs that have visit the SITE_CODE 101? GRRR
SELECT HOL_DETAILS.HOL_CODE, HOL_DETAILS.DESCRIPTION, HOL_DETAILS.DURATION, SITE_DETAILS.SITE_NAME
FROM HOL_DETAILS, SITES_VISTED, SITE_DETAILS
WHERE SITES_VISITED.SITE_CODE = 101
ORDER BY HOL_DETAILS.HOL_CODE;
Is simply returning a list of every single HOL_CODE combined with every single SITE_CODE?
It should only show the HOL_CODEs that have visit the SITE_CODE 101? GRRR
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff