Hey there! Sign in to join this conversationNew here? Join for free
    • Thread Starter
    Offline

    18
    ReputationRep:
    I've been asked to write an SQL statement that will count the number of current policies that has a policy within current coverage.. Meaning that the date lies between effective and expiry date.

    Policy is also referenced as currently insured.

    Anyone got any idea? :eek:
    Offline

    21
    ReputationRep:
    select count(*) from policy where effective_date < sysdate and expiry_date > sysdate
    • Thread Starter
    Offline

    18
    ReputationRep:
    (Original post by shawn_o1)
    select count(*) from policy where effective_date < sysdate and expiry_date > sysdate

    I'm not entirely sure.. Especially since it's taken people in the class a good several hours.. Even for those with a few years experience in SQL
    Offline

    21
    ReputationRep:
    oh i see, I can't use count without an aggregate group by function.

    how about:
    create view select_policy as select * from policy where effective_date < sysdate and expiry_date > sysdate;
    select count(*) from select_policy;
    • Thread Starter
    Offline

    18
    ReputationRep:
    (Original post by shawn_o1)
    oh i see, I can't use count without an aggregate function.

    how about:
    create view select_policy as select * from policy where effective_date < sysdate and expiry_date > sysdate;
    select count(*) from select_policy;

    Okay thanks!! When I access the uni server tomorrow I will check it out to see if it works or not!
    Offline

    0
    ReputationRep:
    SELECT COUNT( expression )
    FROM tables
    WHERE conditions;

    While seeing this syntax, hopefully you can do.

    Good Luck.....!


    0
 
 
 
Poll
Do you agree with the PM's proposal to cut tuition fees for some courses?

The Student Room, Get Revising and Marked by Teachers are trading names of The Student Room Group Ltd.

Register Number: 04666380 (England and Wales), VAT No. 806 8067 22 Registered Office: International House, Queens Road, Brighton, BN1 3XE

Write a reply...
Reply
Hide
Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.