Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Count

Author  Topic 

divan
Posting Yak Master

153 Posts

Posted - 2013-04-04 : 08:04:42
I have the following script

SELECT E.CLIENT_NUMBER,P.AGENCY_ID---,A.AGENCY_NAME
FROM POLICY P
INNER JOIN MPL_DETAIL M ON P.POLICY_NUMBER = M.POLICY_NUMBER AND P.POLICY_DATE_TIME = M.POLICY_DATE_TIME
INNER JOIN REGISTER R ON P.POLICY_NUMBER = R.POLICY_NUMBER
AND P.POLICY_DATE_TIME = R.POLICY_DATE_TIME
INNER JOIN MPL_EXPOSURE E ON R.POLICY_NUMBER = E.POLICY_NUMBER
AND R.POLICY_DATE_TIME = E.POLICY_DATE_TIME
AND E.TERMINATION_DATE IS NULL
WHERE R.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME)
FROM REGISTER R2
WHERE R.POLICY_NUMBER = R2.POLICY_NUMBER
AND R.PORTFOLIO_SET = R2.PORTFOLIO_SET
AND R2.CHECK_OUT IS NULL)

AND P.POL_EFF_DATE < '6/1/13'
AND P.POL_EXP_DATE > '4/4/13'
AND ((E.EXPOSURE_TYPE = '1' AND E.TYPE_LMT <> '8')
OR (E.EXPOSURE_TYPE = '7')
OR (E.EXPOSURE_TYPE = '2' AND E.TYPE_LMT = '9'))
AND R.STATUS_1 NOT IN ('4','6','7')
AND LEFT(P.POLICY_NUMBER,1) <> 'Q'
-- AND EXECUTIVE_EXPORT.STATE = M.RATING_STATE),0)
ORDER BY AGENCY_ID

which gives me a result like the following
3581 1 KY
3679 1 KY
8584 1 TXA&A
6398 1 TXA&A
2030 1 TXASA
14580 1 TXBIP
258 1 TXCDL
2669 1 TXCOM
2681 1 TXCOM
2692 1 TXCOM
2711 1 TXCOM
2745 1 TXCOM

Instead of listing all the above is there a way I can get a count for each agency id

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-04 : 08:08:24
provide headers/column names of the result set e.g.


3581 1 KY

waht is 3581?
What is 1?
what is KY?

additionally your select has two columns in list where as the given result shows to be of three columns.

Cheers
MIK
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 2013-04-04 : 08:21:24
3581 is the E.CLIENT_NUMBER and 1 KY is P.Agency ID.. So anything with a 1 in front of it is AGENCY_id the the other E>CLIENT_NUMBER so there is only two columns listed but it was confusing sorry about that.. SO
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 2013-04-04 : 09:14:28
someone please help.....
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-04 : 09:37:52
may be this?
SELECT COUNT(E.CLIENT_NUMBER),P.AGENCY_ID--, A.AGENCY_NAME
FROM POLICY P
INNER JOIN MPL_DETAIL M ON P.POLICY_NUMBER = M.POLICY_NUMBER AND P.POLICY_DATE_TIME = M.POLICY_DATE_TIME
INNER JOIN REGISTER R ON P.POLICY_NUMBER = R.POLICY_NUMBER
AND P.POLICY_DATE_TIME = R.POLICY_DATE_TIME
INNER JOIN MPL_EXPOSURE E ON R.POLICY_NUMBER = E.POLICY_NUMBER
AND R.POLICY_DATE_TIME = E.POLICY_DATE_TIME
AND E.TERMINATION_DATE IS NULL
WHERE R.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME) FROM REGISTER R2 WHERE R.POLICY_NUMBER = R2.POLICY_NUMBER AND R.PORTFOLIO_SET = R2. PORTFOLIO_SET AND R2.CHECK_OUT IS NULL)
AND P.POL_EFF_DATE < '6/1/13'
AND P.POL_EXP_DATE > '4/4/13'
AND ((E.EXPOSURE_TYPE = '1' AND E.TYPE_LMT <> '8')
OR (E.EXPOSURE_TYPE = '7')
OR (E.EXPOSURE_TYPE = '2' AND E.TYPE_LMT = '9'))
AND R.STATUS_1 NOT IN ('4','6','7')
AND LEFT(P.POLICY_NUMBER,1) <> 'Q'
-- AND EXECUTIVE_EXPORT.STATE = M.RATING_STATE),0)
GROUP BY P.AGENCY_ID--, A.AGENCY_NAME
ORDER BY AGENCY_ID
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 2013-04-04 : 10:12:33
No this gives me total count of Client_number I need total of each different AGENCY_ID
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-04 : 10:40:28
What are you trying to count against an Agency_ID? is it number of clients .. or something else? eleborate it .. If possible come up with sample data (in the form of Insert statements going into some temporay table) and desired output based on that sample data. So that we can use the sample data and suggest a desired query for it.

Cheers
MIK
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 2013-04-04 : 11:19:25
When I run the script I get the following

CLIENT_NUMBER AGENCY_ID
3581 1 KY
3679 1 KY
8584 1 TXA&A
6398 1 TXA&A
2030 1 TXASA
14580 1 TXBIP
258 1 TXCDL
2669 1 TXCOM
2681 1 TXCOM
2692 1 TXCOM
2711 1 TXCOM
2745 1 TXCOM

so I want to know how many CLIENT_NUMBER per AGENCY_ID

Based on the info I have provided I need to see a result like

2 1 KY where the 2 is the number of client number assigned to AGENCY_ID 1 KY
3 1 TXA&A where the 3 is the number of client number assigned to AGENCY_ID 1 TXA&A and so on....
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-04 : 11:41:21
I think then what Bandi suggested should have worked.

SELECT Count(Client_Number),Agency_ID
FROM Table --All joining/condition logic goes here
Group By Agency_ID

Cheers
MIK
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 2013-04-04 : 11:59:30
Thanks that worked...
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-05 : 00:57:42
what i posted is also same.. I think you doesn't have AGENCY_NAME per AGENCY_ID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-05 : 01:20:32
quote:
Originally posted by bandi

may be this?
SELECT COUNT(E.CLIENT_NUMBER),P.AGENCY_ID--, A.AGENCY_NAME
FROM POLICY P
INNER JOIN MPL_DETAIL M ON P.POLICY_NUMBER = M.POLICY_NUMBER AND P.POLICY_DATE_TIME = M.POLICY_DATE_TIME
INNER JOIN REGISTER R ON P.POLICY_NUMBER = R.POLICY_NUMBER
AND P.POLICY_DATE_TIME = R.POLICY_DATE_TIME
INNER JOIN MPL_EXPOSURE E ON R.POLICY_NUMBER = E.POLICY_NUMBER
AND R.POLICY_DATE_TIME = E.POLICY_DATE_TIME
AND E.TERMINATION_DATE IS NULL
WHERE R.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME) FROM REGISTER R2 WHERE R.POLICY_NUMBER = R2.POLICY_NUMBER AND R.PORTFOLIO_SET = R2. PORTFOLIO_SET AND R2.CHECK_OUT IS NULL)
AND P.POL_EFF_DATE < '6/1/13'
AND P.POL_EXP_DATE > '4/4/13'

AND ((E.EXPOSURE_TYPE = '1' AND E.TYPE_LMT <> '8')
OR (E.EXPOSURE_TYPE = '7')
OR (E.EXPOSURE_TYPE = '2' AND E.TYPE_LMT = '9'))
AND R.STATUS_1 NOT IN ('4','6','7')
AND LEFT(P.POLICY_NUMBER,1) <> 'Q'
-- AND EXECUTIVE_EXPORT.STATE = M.RATING_STATE),0)
GROUP BY P.AGENCY_ID--, A.AGENCY_NAME
ORDER BY AGENCY_ID


Always try to pass date values in umambiguos formats

using format like above can cause dates to be interpreted differently by the server depending on its language settings

for ex '6/1/13' can mean 6th Jan 2013 or 1st June 2013 depending on server regional setting

make sure you read this

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

to avoid such issues make it like

..
P.POL_EFF_DATE < '20130601'
AND P.POL_EXP_DATE > '20130404'
...


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -