SQL assistance please

[justlivyalife]

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?

[grumbledoak]

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:
SELECT COUNT(*)
FROM "HOL_DETAILS"
WHERE "HOL_DETAILS"."DESTINATION" = USA;

You select from a table, not a column.

[Famous Graham]

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

Edited by Famous Graham on Wednesday 9th January 13:45

[hawkaye]

or try

select (*) from hol_details where destination = "USA"

[justlivyalife]

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!

Any ideas how to do a group by query on the same column - need to count for each destination (Spain, France, Canada, USA)?

[justlivyalife]

Sorted! Thanks all - never realised there were so many knowledgeable people here .

[tigger1]

SELECT destination, COUNT(*)
FROM HOL_DETAILS
group by destination

That should do it.

Edited - cos I'm a spacktard.

Edited by tigger1 on Wednesday 9th January 14:11

[wiggy001]

tigger1 said:

SELECT destination, COUNT(*)
FROM HOL_DETAILS
WHERE DESTINATION = 'USA'
group by destination

That should do it.

Or

SELECT destination, COUNT(*)
FROM HOL_DETAILS
group by destination

if my interpretation of the question is correct

[whygee02]

tigger1 said:

SELECT destination, COUNT(*)
FROM HOL_DETAILS
WHERE DESTINATION = 'USA'
group by destination

That should do it.

That'll only return the same USA count

SELECT destination, COUNT(*) FROM HOL_DETAILS group by destination order by destination

should do em all

[Famous Graham]

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!

Quote text strings, don't quote integers.

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

[justlivyalife]

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?

[tigger1]

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?

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

[justlivyalife]

For some reason I couldn't get it to sort them - was using GROUP BY not order by.

Thanks once again .

[justlivyalife]

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

[sgrimshaw]

Doesn't look like you have joined the tables.

[tigger1]

There's no join between the tables. (see earlier examples)

edit: That'll teach me to open a thread, read something else and then reply!

Edited by tigger1 on Wednesday 9th January 15:48