Problem with count() in query

[hadoob024]

I'm having problems writing this one particular query.  I have two tables, CARRIER and PATIENTS.  There's a one-to-many relationship between the two.  Basically, in one query, I want to pull the CARRIERNAME and CARRIERADDRESS of every CARRIER record in addition to a count of how many PATIENTS records have that particular CARRIERID.  Does that make sense?  So far I've tried:

 

SELECT ic.id,ic.name,ic.carrieraddress,ic.carriercity,ic.carrierstate,ipp.id
FROM carrier ic, patient_procedure ipp
WHERE ic.deleted=0 AND
ic.id = ipp.carrier_id
ORDER BY ic.name;

 

Which doesn't work, and:

 

SELECT ic.id,ic.name,ic.carrieraddress,ic.carriercity,ic.carrierstate,count(ipp.id)
FROM carrier ic, patient_procedure ipp
WHERE ic.deleted=0 AND
ic.id = ipp.carrier_id
ORDER BY ic.name;

 

 

Which also doesn't work.  Any thoughts on where I'm going wrong?  Thanks!

[bh]

The problem is in your main sql file in the 42. line!

 

Table structure or sg? Or we guess the error? 

[hadoob024]

Huh?  Sorry.  I don't get what you're asking.  Here's the table structure though for both tables:

 

CARRIER:

id

name

carrieraddress

carriercity

carrierstate

 

 

PATIENT_PROCEDURE:

id

name

carrier_id

 

 

So I need a list of every carrier along with a count of how many patient_procedure records have that particular carrier_id.

[bh]

Wheres the 'deleted' column from your structure?

[hadoob024]

Oops.  My bad.  I forgot that one.  Here it is updated:

 

CARRIER:

id

name

carrieraddress

carriercity

carrierstate

deleted

 

 

PATIENT_PROCEDURE:

id

name

carrier_id

[bh]

Is there any error message?

[hadoob024]

Nope.  No error message.  It's just that the result set is not what I was looking for.  I want my result set to look like:

 

ID      NAME      CARRIERADDRESS      CARRIERSTATE      CARRIERCITY      count of patient_procedures

123    BCBS      999 main st.                TX                        Dallas                15

234    Cigna      123 main st.              AZ                        Phoenix              31

.

.

.

 

And so on.

[bh]

 

okey, and then?

 

post your php code. probably your fetch mode is not what you like...

 

and user 'AS' like that:

 

SELECT ic.id AS ID,ic.name AS NAME,ic.carrieraddress AS CARRIERADDRESS... and so on

[hadoob024]

Here's the query:

SELECT ic.id,ic.name,ic.carrieraddress,ic.carriercity,ic.carrierstate,ipp.id
FROM carrier ic, patient_procedure ipp
WHERE ic.deleted=0 AND
ic.id = ipp.carrier_id
ORDER BY ic.name;

 

 

Spits out the result set:

 

ID      NAME      CARRIERADDRESS      CARRIERSTATE      CARRIERCITY      PATIENT_PROCEDURE ID

123    BCBS      999 main st.                TX                        Dallas                  123

123    BCBS      999 main st.                TX                        Dallas                  234

123    BCBS      999 main st.                TX                        Dallas                  345

123    BCBS      999 main st.                TX                        Dallas                  456

234    Cigna      123 main st.                AZ                        Phoenix              345

234    Cigna      123 main st.                AZ                        Phoenix              456

234    Cigna      123 main st.                AZ                        Phoenix              567

234    Cigna      123 main st.                AZ                        Phoenix              678

.

.

.

 

 

But I need a result set like the following:

 

ID      NAME      CARRIERADDRESS      CARRIERSTATE      CARRIERCITY      count of patient_procedures

123    BCBS      999 main st.                TX                        Dallas                5

234    Cigna      123 main st.                AZ                        Phoenix              4

.

.

.

 

[bh]

Group By

[hadoob024]

Perfect!!!  That worked.  My final query (which works) is:

 

SELECT ic.id,ic.name,ic.carrieraddress,ic.carriercity,ic.carrierstate,count(ipp.id)
FROM carrier ic, patient_procedure ipp
WHERE ic.deleted=0 AND
ic.id = ipp.carrier_id
GROUP BY ic.id
ORDER BY ic.name;