caseys_32
04-11-2007, 11:09 AM
I am running MySQL v5.0. I have a very simple DB with three tables:
CREATE TABLE patient (
PatientID int(11) NOT NULL auto_increment,
AccountNum int(11) default NULL,
PRIMARY KEY (PatientID)
);
CREATE TABLE `Visit` (
`VisitID` int(11) NOT NULL auto_increment,
`Price` double default NULL,
`CPT4` varchar(45) default NULL,
`CPT4Desc` varchar(100) default NULL,
PRIMARY KEY (`VisitID`),
);
CREATE TABLE patientVisit (
PatientID int(11) NOT NULL,
VisitID int(11) NOT NULL,
Foreign KEY (PatientID) references Patient,
Foreign Key (VisitID) references Visit
);
What I am looking to do is group by CPT4's. The first query joins the sample data.
select accountnum, price, cpt4, cpt4desc
from patient a, patientvisit b, visit c
where a.patientid = b.patientid
and b.visitid = c.visitid;
+------------+--------+--------+----------+
| accountnum | price | cpt4 | cpt4desc |
+------------+--------+--------+----------+
| 111 | 200 | 1 | test1 |
| 111 | 500 | 2 | test2 |
| 112 | 200 | 1 | test1 |
| 112 | 500 | 2 | test2 |
| 113 | 500 | 3 | test3 |
+------------+--------+--------+----------+
I can get the number of lines per account, but I want to have a 4th column showing the cpt4's that are inculded in the total price.
select accountnum, sum(price), count(*)
from
(
select accountnum, price, cpt4, cpt4desc
from patient a, patientvisit b, visit c
where a.patientid = b.patientid
and b.visitid = c.visitid) as a
group by accountnum;
+------------+------------+----------+
| accountnum | sum(price) | count(*) |
+------------+------------+----------+
| 111 | 700 | 2 |
| 112 | 700 | 2 |
| 113 | 500 | 1 |
+------------+------------+----------+
Desired Result:
+------------+------------+----------+--------+
| accountnum | sum(price) | count(*) | CPT4's |
+------------+------------+----------+--------+
| 111 | 700 | 2 | 1,2 |
| 112 | 700 | 2 | 1,2 |
| 113 | 500 | 1 | 3 |
+------------+------------+----------+--------+
How would I inculde all the CPT4's in one column? I looked into Pivioting the data but I have thousands of CPT4s and MySQL doesnt have built in pivot functionality. Having thousands of columns makes it impossible to do using CASE statements.
I appreciate the help!
Casey
CREATE TABLE patient (
PatientID int(11) NOT NULL auto_increment,
AccountNum int(11) default NULL,
PRIMARY KEY (PatientID)
);
CREATE TABLE `Visit` (
`VisitID` int(11) NOT NULL auto_increment,
`Price` double default NULL,
`CPT4` varchar(45) default NULL,
`CPT4Desc` varchar(100) default NULL,
PRIMARY KEY (`VisitID`),
);
CREATE TABLE patientVisit (
PatientID int(11) NOT NULL,
VisitID int(11) NOT NULL,
Foreign KEY (PatientID) references Patient,
Foreign Key (VisitID) references Visit
);
What I am looking to do is group by CPT4's. The first query joins the sample data.
select accountnum, price, cpt4, cpt4desc
from patient a, patientvisit b, visit c
where a.patientid = b.patientid
and b.visitid = c.visitid;
+------------+--------+--------+----------+
| accountnum | price | cpt4 | cpt4desc |
+------------+--------+--------+----------+
| 111 | 200 | 1 | test1 |
| 111 | 500 | 2 | test2 |
| 112 | 200 | 1 | test1 |
| 112 | 500 | 2 | test2 |
| 113 | 500 | 3 | test3 |
+------------+--------+--------+----------+
I can get the number of lines per account, but I want to have a 4th column showing the cpt4's that are inculded in the total price.
select accountnum, sum(price), count(*)
from
(
select accountnum, price, cpt4, cpt4desc
from patient a, patientvisit b, visit c
where a.patientid = b.patientid
and b.visitid = c.visitid) as a
group by accountnum;
+------------+------------+----------+
| accountnum | sum(price) | count(*) |
+------------+------------+----------+
| 111 | 700 | 2 |
| 112 | 700 | 2 |
| 113 | 500 | 1 |
+------------+------------+----------+
Desired Result:
+------------+------------+----------+--------+
| accountnum | sum(price) | count(*) | CPT4's |
+------------+------------+----------+--------+
| 111 | 700 | 2 | 1,2 |
| 112 | 700 | 2 | 1,2 |
| 113 | 500 | 1 | 3 |
+------------+------------+----------+--------+
How would I inculde all the CPT4's in one column? I looked into Pivioting the data but I have thousands of CPT4s and MySQL doesnt have built in pivot functionality. Having thousands of columns makes it impossible to do using CASE statements.
I appreciate the help!
Casey