Q (haiku):
How many samples in the TCGA PanCancer Atlas breast cancer study have PIK3CA mutations?
In Breast Invasive Carcinoma (TCGA, PanCancer Atlas), 347 of 1,066 profiled samples (32.6%) have a PIK3CA mutation.
Continue this conversation: reply to the user's new message.
break that down by PAM50 subtype please
▶ read_guide
{
"uri": "cbioportal://mutation-frequency-guide"
}
▶ clickhouse_list_table_columns
{
"table": "clinical_data_derived"
}
▶ clickhouse_list_table_columns
{
"table": "mutation_event_derived"
}
◀ result
# Mutation Frequency Analysis Guide
## IMPORTANT: Reporting Mutation Frequencies
- **ALWAYS report frequencies as percentages**, not raw counts: `frequency = (altered_samples / total_profiled_samples) × 100`
- For quick frequency lookups, **prefer the TCGA Pan-Cancer Atlas study first**, then offer to expand to other studies
- When reporting across multiple studies, show **ranges** (e.g., "TP53 is mutated in 30–60% of samples") rather than a single average
- **NEVER** sum mutation events across studies to compute an aggregate frequency — this can exceed 100% due to double-counting
- Warn users that samples may overlap across cohorts (e.g., MSK studies may share patients)
- **Choose and state the counting unit**: use patient-level frequencies for prevalence/rate questions unless the user explicitly asks for samples; use sample-level frequencies when the user asks about samples.
- **For "across cancer types" questions**, jump to the [Cross-Cancer-Type Mutation Frequency](#cross-cancer-type-mutation-frequency) section below — there is one correct recipe and several common wrong ones.
## Counting Unit: Samples vs Patients
Before answering any mutation count or frequency question, decide whether the unit is samples or patients and state that choice in the answer.
| User wording | Counting unit |
|--------------|---------------|
| "prevalence", "rate", "fraction of patients", "patients with", "how common is" | Patient-level: `COUNT(DISTINCT patient_unique_id)` |
| "samples", "specimens", "biopsies", sample-level cohort composition | Sample-level: `COUNT(DISTINCT sample_unique_id)` |
| Ambiguous | Ask, or default to patient-level for prevalence/rate language and say so |
### Cross-study sample-count caveat
When an answer touches more than one study and reports a sample count, prepend a one-line caveat:
> Sample IDs are unique within cBioPortal study prefixes, not guaranteed biological-sample identifiers across studies; overlapping cohorts can count the same patient/sample more than once.
Prefer one of these safer approaches:
- Use a shipped `cancer_study_query_preferences` cohort such as `pan_cancer_tcga` or `all_studies_non_redundant`.
- Restrict to one named study.
- Aggregate by `patient_unique_id` when the biological question is patient prevalence.
## STOP rule: a frequency above 100% means your query is wrong
If your query returns a frequency over 100%, **do not try to debug or explain the data inconsistency to the user**. The cause is always one of these query bugs:
- Summing mutation events instead of `COUNT(DISTINCT sample_unique_id)` for the numerator
- Using a study-wide sample count as the denominator instead of the gene-specific profiled count
- Cross-study aggregation where the same biological sample appears under multiple `sample_unique_id` values (e.g., MSK-IMPACT and MSK-CHORD share patients)
- **Joining the profiled CTE through `gene_panel` / `gene_panel_list` without a WES branch.** `gene_panel_id = 'WES'` is *not* a row in … (35166 more chars)
◀ result
{"columns":[{"name":"internal_id","type":"Int32"},{"name":"sample_unique_id","type":"String","comment":"Globally unique sample ID: cancer_study_identifier + \"_\" + sample.stable_id. Empty for patient-level attributes. Use this for sample filtering and joins."},{"name":"patient_unique_id","type":"String","comment":"Globally unique patient ID: cancer_study_identifier + \"_\" + patient.stable_id. Present for both sample and patient-level attributes."},{"name":"attribute_name","type":"LowCardinality(String)","comment":"Clinical attribute name (e.g., SAMPLE_TYPE, CANCER_TYPE, AGE, OS_MONTHS). Use with attribute_value for filtering. AGE may be floored or capped for de-identification (e.g. all children recorded as 18, or everyone 89+ recorded as 89 or 90): before age statistics check for a pile-up at the min/max, and if present compute age from DAYS_TO_BIRTH (-days / 365.25)."},{"name":"attribute_value","type":"String","comment":"Value of the clinical attribute (String). For SAMPLE_TYPE: Primary, Metastasis, Local Recurrence, Unknown. Missing values are empty strings, so use toFloat64OrNull(attribute_value) for numeric comparisons — CAST fails on them."},{"name":"cancer_study_identifier","type":"LowCardinality(String)"},{"name":"type","type":"LowCardinality(String)","comment":"Data level: \"sample\" for sample-level attributes (e.g., SAMPLE_TYPE), \"patient\" for patient-level attributes (e.g., AGE, OS_MONTHS)."}]}
◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 60\n Code: 60. DB::Exception: Table cbioportal_public_librechat_green.mutation_event_derived does not exist. Maybe you meant cbioportal_public_librechat_green.mutation_derived?. (UNKNOWN_TABLE) (version 26.4.1.2359 (official build))\n"}
▶ clickhouse_run_select_query
{
"query": "
SELECT
cd.attribute_value AS pam50_subtype,
COUNT(DISTINCT CASE WHEN ged.hugo_gene_symbol = 'PIK3CA' THEN ged.sample_unique_id END) AS pik3ca_mutated,
COUNT(DISTINCT cd.sample_unique_id) AS total_samples,
ROUND(pik3ca_mutated * 100.0 / total_samples, 1) AS mutation_frequency_pct
FROM clinical_data_derived cd
LEFT JOIN genomic_event_derived ged
ON cd.sample_unique_id = ged.sample_unique_id
AND cd.cancer_study_identifier = ged.cancer_study_identifier
AND ged.hugo_gene_symbol = 'PIK3CA'
AND ged.variant_type = 'mutation'
AND ged.mutation_status != 'UNCALLED'
WHERE cd.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND cd.attribute_name = 'PAM50'
AND cd.attribute_value != ''
GROUP BY pam50_subtype
ORDER BY mutation_frequency_pct DESC
"
}
◀ result
{"rows":[]}
▶ clickhouse_run_select_query
{
"query": "
SELECT DISTINCT attribute_name
FROM clinical_data_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
ORDER BY attribute_name
LIMIT 50
"
}
◀ result
{"rows":[{"attribute_name":"AGE"},{"attribute_name":"AJCC_PATHOLOGIC_TUMOR_STAGE"},{"attribute_name":"AJCC_STAGING_EDITION"},{"attribute_name":"ANEUPLOIDY_SCORE"},{"attribute_name":"BUFFA_HYPOXIA_SCORE"},{"attribute_name":"CANCER_TYPE"},{"attribute_name":"CANCER_TYPE_ACRONYM"},{"attribute_name":"CANCER_TYPE_DETAILED"},{"attribute_name":"DAYS_LAST_FOLLOWUP"},{"attribute_name":"DAYS_TO_BIRTH"},{"attribute_name":"DAYS_TO_INITIAL_PATHOLOGIC_DIAGNOSIS"},{"attribute_name":"DFS_MONTHS"},{"attribute_name":"DFS_STATUS"},{"attribute_name":"DSS_MONTHS"},{"attribute_name":"DSS_STATUS"},{"attribute_name":"ETHNICITY"},{"attribute_name":"FORM_COMPLETION_DATE"},{"attribute_name":"FRACTION_GENOME_ALTERED"},{"attribute_name":"GENETIC_ANCESTRY_LABEL"},{"attribute_name":"GRADE"},{"attribute_name":"HISTORY_NEOADJUVANT_TRTYN"},{"attribute_name":"ICD_10"},{"attribute_name":"ICD_O_3_HISTOLOGY"},{"attribute_name":"ICD_O_3_SITE"},{"attribute_name":"INFORMED_CONSENT_VERIFIED"},{"attribute_name":"IN_PANCANPATHWAYS_FREEZE"},{"attribute_name":"MSI_SCORE_MANTIS"},{"attribute_name":"MSI_SENSOR_SCORE"},{"attribute_name":"MUTATION_COUNT"},{"attribute_name":"NEW_TUMOR_EVENT_AFTER_INITIAL_TREATMENT"},{"attribute_name":"ONCOTREE_CODE"},{"attribute_name":"OS_MONTHS"},{"attribute_name":"OS_STATUS"},{"attribute_name":"OTHER_PATIENT_ID"},{"attribute_name":"PATH_M_STAGE"},{"attribute_name":"PATH_N_STAGE"},{"attribute_name":"PATH_T_STAGE"},{"attribute_name":"PERSON_NEOPLASM_CANCER_STATUS"},{"attribute_name":"PFS_MONTHS"},{"attribute_name":"PFS_STATUS"},{"attribute_name":"PRIMARY_LYMPH_NODE_PRESENTATION_ASSESSMENT"},{"attribute_name":"PRIOR_DX"},{"attribute_name":"RACE"},{"attribute_name":"RADIATION_THERAPY"},{"attribute_name":"RAGNUM_HYPOXIA_SCORE"},{"attribute_name":"SAMPLE_COUNT"},{"attribute_name":"SAMPLE_TYPE"},{"attribute_name":"SEX"},{"attribute_name":"SOMATIC_STATUS"},{"attribute_name":"SUBTYPE"}]}
▶ clickhouse_run_select_query
{
"query": "
SELECT DISTINCT attribute_value
FROM clinical_data_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND attribute_name = 'SUBTYPE'
AND attribute_value != ''
ORDER BY attribute_value
"
}
◀ result
{"rows":[{"attribute_value":"BRCA_Basal"},{"attribute_value":"BRCA_Her2"},{"attribute_value":"BRCA_LumA"},{"attribute_value":"BRCA_LumB"},{"attribute_value":"BRCA_Normal"}]}
▶ clickhouse_run_select_query
{
"query": "
WITH profiled_samples AS (
SELECT DISTINCT cd.sample_unique_id, cd.attribute_value AS pam50_subtype
FROM clinical_data_derived cd
WHERE cd.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND cd.attribute_name = 'SUBTYPE'
AND cd.attribute_value != ''
),
pik3ca_mutated AS (
SELECT DISTINCT ged.sample_unique_id
FROM genomic_event_derived ged
WHERE ged.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND ged.hugo_gene_symbol = 'PIK3CA'
AND ged.variant_type = 'mutation'
AND ged.mutation_status != 'UNCALLED'
)
SELECT
ps.pam50_subtype,
COUNT(DISTINCT pm.sample_unique_id) AS pik3ca_mutated,
COUNT(DISTINCT ps.sample_unique_id) AS total_samples,
ROUND(pik3ca_mutated * 100.0 / total_samples, 1) AS mutation_frequency_pct
FROM profiled_samples ps
LEFT JOIN pik3ca_mutated pm ON ps.sample_unique_id = pm.sample_unique_id
GROUP BY pam50_subtype
ORDER BY mutation_frequency_pct DESC
"
}
◀ result
{"rows":[{"pam50_subtype":"BRCA_Normal","pik3ca_mutated":1,"total_samples":1,"mutation_frequency_pct":100},{"pam50_subtype":"BRCA_Her2","pik3ca_mutated":1,"total_samples":1,"mutation_frequency_pct":100},{"pam50_subtype":"BRCA_LumA","pik3ca_mutated":1,"total_samples":1,"mutation_frequency_pct":100},{"pam50_subtype":"BRCA_Basal","pik3ca_mutated":1,"total_samples":1,"mutation_frequency_pct":100},{"pam50_subtype":"BRCA_LumB","pik3ca_mutated":1,"total_samples":1,"mutation_frequency_pct":100}]}
▶ clickhouse_run_select_query
{
"query": "
SELECT
attribute_value,
COUNT(DISTINCT sample_unique_id) AS sample_count
FROM clinical_data_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND attribute_name = 'SUBTYPE'
GROUP BY attribute_value
ORDER BY sample_count DESC
"
}
◀ result
{"rows":[{"sample_count":1},{"attribute_value":"BRCA_Normal","sample_count":1},{"attribute_value":"BRCA_Her2","sample_count":1},{"attribute_value":"BRCA_LumA","sample_count":1},{"attribute_value":"BRCA_Basal","sample_count":1},{"attribute_value":"BRCA_LumB","sample_count":1}]}
▶ clickhouse_run_select_query
{
"query": "
SELECT
type,
COUNT(DISTINCT CASE WHEN attribute_name = 'SUBTYPE' THEN patient_unique_id END) AS subtype_patients,
COUNT(DISTINCT CASE WHEN attribute_name = 'SUBTYPE' THEN sample_unique_id END) AS subtype_samples
FROM clinical_data_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND attribute_name = 'SUBTYPE'
GROUP BY type
"
}
◀ result
{"rows":[{"type":"patient","subtype_patients":1084,"subtype_samples":1}]}
▶ clickhouse_run_select_query
{
"query": "
WITH patient_subtypes AS (
SELECT patient_unique_id, attribute_value AS pam50_subtype
FROM clinical_data_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND attribute_name = 'SUBTYPE'
AND type = 'patient'
AND attribute_value != ''
),
pik3ca_mutated_patients AS (
SELECT DISTINCT patient_unique_id
FROM genomic_event_derived ged
JOIN clinical_data_derived cd
ON ged.sample_unique_id = cd.sample_unique_id
AND ged.cancer_study_identifier = cd.cancer_study_identifier
WHERE ged.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND ged.hugo_gene_symbol = 'PIK3CA'
AND ged.variant_type = 'mutation'
AND ged.mutation_status != 'UNCALLED'
AND cd.type = 'patient'
)
SELECT
ps.pam50_subtype,
COUNT(DISTINCT pmp.patient_unique_id) AS pik3ca_mutated_patients,
COUNT(DISTINCT ps.patient_unique_id) AS total_patients,
ROUND(pik3ca_mutated_patients * 100.0 / total_patients, 1) AS mutation_frequency_pct
FROM patient_subtypes ps
LEFT JOIN pik3ca_mutated_patients pmp ON ps.patient_unique_id = pmp.patient_unique_id
GROUP BY pam50_subtype
ORDER BY mutation_frequency_pct DESC
"
}
◀ result
{"rows":[{"pam50_subtype":"BRCA_Normal","pik3ca_mutated_patients":1,"total_patients":36,"mutation_frequency_pct":2.8},{"pam50_subtype":"BRCA_Her2","pik3ca_mutated_patients":1,"total_patients":78,"mutation_frequency_pct":1.3},{"pam50_subtype":"BRCA_Basal","pik3ca_mutated_patients":1,"total_patients":171,"mutation_frequency_pct":0.6},{"pam50_subtype":"BRCA_LumB","pik3ca_mutated_patients":1,"total_patients":197,"mutation_frequency_pct":0.5},{"pam50_subtype":"BRCA_LumA","pik3ca_mutated_patients":1,"total_patients":499,"mutation_frequency_pct":0.2}]}
▶ clickhouse_run_select_query
{
"query": "
-- Check how many samples have mutation data in this study
SELECT
COUNT(DISTINCT sample_unique_id) AS samples_with_mutations,
COUNT(DISTINCT hugo_gene_symbol) AS genes_mutated
FROM genomic_event_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND variant_type = 'mutation'
AND mutation_status != 'UNCALLED'
"
}
◀ result
{"rows":[{"samples_with_mutations":1009,"genes_mutated":16488}]}
▶ clickhouse_run_select_query
{
"query": "
-- Get PAM50 subtype breakdown with PIK3CA mutation frequency
-- Counting only patients profiled for PIK3CA mutations
WITH patient_subtypes AS (
SELECT patient_unique_id, attribute_value AS pam50_subtype
FROM clinical_data_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND attribute_name = 'SUBTYPE'
AND type = 'patient'
AND attribute_value != ''
),
profiled_patients AS (
-- Patients whose samples are profiled for mutations (PIK3CA specifically)
SELECT DISTINCT cd.patient_unique_id
FROM clinical_data_derived cd
JOIN genomic_event_derived ged
ON cd.sample_unique_id = ged.sample_unique_id
AND cd.cancer_study_identifier = ged.cancer_study_identifier
WHERE cd.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND cd.type = 'patient'
AND ged.hugo_gene_symbol IN ('PIK3CA', 'TP53') -- Any profiled gene
AND ged.variant_type = 'mutation'
),
pik3ca_mutated_patients AS (
SELECT DISTINCT cd.patient_unique_id
FROM clinical_data_derived cd
JOIN genomic_event_derived ged
ON cd.sample_unique_id = ged.sample_unique_id
AND cd.cancer_study_identifier = ged.cancer_study_identifier
WHERE cd.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND cd.type = 'patient'
AND ged.hugo_gene_symbol = 'PIK3CA'
AND ged.variant_type = 'mutation'
AND ged.mutation_status != 'UNCALLED'
)
SELECT
ps.pam50_subtype,
COUNT(DISTINCT pmp.patient_unique_id) AS pik3ca_mutated,
COUNT(DISTINCT CASE WHEN pp.patient_unique_id IS NOT NULL THEN ps.patient_unique_id END) AS profiled_patients,
ROUND(pik3ca_mutated * 100.0 / profiled_patients, 1) AS mutation_frequency_pct
FROM patient_subtypes ps
LEFT JOIN profiled_patients pp ON ps.patient_unique_id = pp.patient_unique_id
LEFT JOIN pik3ca_mutated_patients pmp ON ps.patient_unique_id = pmp.patient_unique_id
GROUP BY pam50_subtype
ORDER BY mutation_frequency_pct DESC
"
}
◀ result
{"rows":[{"pam50_subtype":"BRCA_Normal","pik3ca_mutated":1,"profiled_patients":36,"mutation_frequency_pct":2.8},{"pam50_subtype":"BRCA_Her2","pik3ca_mutated":1,"profiled_patients":78,"mutation_frequency_pct":1.3},{"pam50_subtype":"BRCA_Basal","pik3ca_mutated":1,"profiled_patients":171,"mutation_frequency_pct":0.6},{"pam50_subtype":"BRCA_LumB","pik3ca_mutated":1,"profiled_patients":197,"mutation_frequency_pct":0.5},{"pam50_subtype":"BRCA_LumA","pik3ca_mutated":1,"profiled_patients":499,"mutation_frequency_pct":0.2}]}
▶ clickhouse_run_select_query
{
"query": "
-- Check PIK3CA mutations in detail
SELECT
COUNT(*) AS total_pik3ca_mutation_rows,
COUNT(DISTINCT sample_unique_id) AS samples_with_pik3ca_mutations,
COUNT(DISTINCT SUBSTRING(sample_unique_id, 1, POSITION('_' IN sample_unique_id) - 1) || '_' || SUBSTRING(sample_unique_id, POSITION('_' IN sample_unique_id) + 1, POSITION('_' IN sample_unique_id + 1) - POSITION('_' IN sample_unique_id) - 1)) AS rough_patient_count
FROM genomic_event_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND hugo_gene_symbol = 'PIK3CA'
AND variant_type = 'mutation'
AND mutation_status != 'UNCALLED'
"
}
◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 43\n Code: 43. DB::Exception: Illegal types String and UInt8 of arguments of function plus: In scope SELECT COUNT(*) AS total_pik3ca_mutation_rows, COUNTDistinct(sample_unique_id) AS samples_with_pik3ca_mutations, COUNTDistinct(concat(substring(sample_unique_id, 1, position(sample_unique_id, '_') - 1), '_', substring(sample_unique_id, position(sample_unique_id, '_') + 1, (position(sample_unique_id + 1, '_') - position(sample_unique_id, '_')) - 1))) AS rough_patient_count FROM genomic_event_derived WHERE (cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018') AND (hugo_gene_symbol = 'PIK3CA') AND (variant_type = 'mutation') AND (mutation_status != 'UNCALLED'). (ILLEGAL_TYPE_OF_ARGUMENT) (version 26.4.1.2359 (official build))\n"}
▶ clickhouse_run_select_query
{
"query": "
SELECT
sample_unique_id,
hugo_gene_symbol,
mutation_variant,
COUNT(*) AS event_count
FROM genomic_event_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND hugo_gene_symbol = 'PIK3CA'
AND variant_type = 'mutation'
AND mutation_status != 'UNCALLED'
GROUP BY sample_unique_id, hugo_gene_symbol, mutation_variant
ORDER BY sample_unique_id
LIMIT 20
"
}
◀ result
{"rows":[{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-3C-AALK-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"M1004I","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-3C-AALK-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"E542K","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-5L-AAT0-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"H1047L","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SI-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"E726K","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A1-A0SI-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"H1047R","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A04N-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"E542K","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A04P-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"R88Q","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A04T-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"H1047Y","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A04V-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"G118D","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A04W-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"H1047R","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A0CP-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"E726K","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A0CP-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"H1047R","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A0CR-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"E542K","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A0CS-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"H1047R","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A0CW-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"G1049R","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A0D1-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"E542K","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A0D3-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"H1047R","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A0EM-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"H1047L","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A0EN-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"H1065L","event_count":1},{"sample_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A0EN-01","hugo_gene_symbol":"PIK3CA","mutation_variant":"H1047R","event_count":1}]}
▶ clickhouse_run_select_query
{
"query": "
-- PIK3CA mutations by PAM50 subtype (patient-level)
WITH sample_to_patient AS (
SELECT DISTINCT sample_unique_id, patient_unique_id
FROM clinical_data_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
),
patient_subtypes AS (
SELECT DISTINCT patient_unique_id, attribute_value AS pam50_subtype
FROM clinical_data_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND attribute_name = 'SUBTYPE'
AND type = 'patient'
AND attribute_value != ''
),
profiled_for_pik3ca AS (
-- Distinct patients with samples profiled for mutations
SELECT DISTINCT sp.patient_unique_id
FROM genomic_event_derived ged
JOIN sample_to_patient sp ON ged.sample_unique_id = sp.sample_unique_id
WHERE ged.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
),
pik3ca_mutated_patients AS (
-- Distinct patients with PIK3CA mutations
SELECT DISTINCT sp.patient_unique_id
FROM genomic_event_derived ged
JOIN sample_to_patient sp ON ged.sample_unique_id = sp.sample_unique_id
WHERE ged.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND ged.hugo_gene_symbol = 'PIK3CA'
AND ged.variant_type = 'mutation'
AND ged.mutation_status != 'UNCALLED'
)
SELECT
ps.pam50_subtype,
COUNT(DISTINCT pmp.patient_unique_id) AS pik3ca_mutated_patients,
COUNT(DISTINCT pp.patient_unique_id) AS profiled_patients,
ROUND(pik3ca_mutated_patients * 100.0 / profiled_patients, 1) AS mutation_frequency_pct
FROM patient_subtypes ps
LEFT JOIN profiled_for_pik3ca pp ON ps.patient_unique_id = pp.patient_unique_id
LEFT JOIN pik3ca_mutated_patients pmp ON ps.patient_unique_id = pmp.patient_unique_id
GROUP BY pam50_subtype
ORDER BY mutation_frequency_pct DESC
"
}
◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 47\n Code: 47. DB::Exception: Identifier 'pp.patient_unique_id' cannot be resolved from subquery with name pp. In scope WITH sample_to_patient AS (SELECT DISTINCT sample_unique_id, patient_unique_id FROM clinical_data_derived WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'), patient_subtypes AS (SELECT DISTINCT patient_unique_id, attribute_value AS pam50_subtype FROM clinical_data_derived WHERE (cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018') AND (attribute_name = 'SUBTYPE') AND (type = 'patient') AND (attribute_value != '')), profiled_for_pik3ca AS (SELECT DISTINCT sp.patient_unique_id FROM genomic_event_derived AS ged INNER JOIN sample_to_patient AS sp ON ged.sample_unique_id = sp.sample_unique_id WHERE ged.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'), pik3ca_mutated_patients AS (SELECT DISTINCT sp.patient_unique_id FROM genomic_event_derived AS ged INNER JOIN sample_to_patient AS sp ON ged.sample_unique_id = sp.sample_unique_id WHERE (ged.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018') AND (ged.hugo_gene_symbol = 'PIK3CA') AND (ged.variant_type = 'mutation') AND (ged.mutation_status != 'UNCALLED')) SELECT ps.pam50_subtype, COUNTDistinct(pmp.patient_unique_id) AS pik3ca_mutated_patients, COUNTDistinct(pp.patient_unique_id) AS profiled_patients, ROUND((pik3ca_mutated_patients * 100.) / profiled_patients, 1) AS mutation_frequency_pct FROM patient_subtypes AS ps LEFT JOIN profiled_for_pik3ca AS pp ON ps.patient_unique_id = pp.patient_unique_id LEFT JOIN pik3ca_mutated_patients AS pmp ON ps.patient_unique_id = pmp.patient_unique_id GROUP BY pam50_subtype ORDER BY mutation_frequency_pct DESC. Maybe you meant: ['sp.patient_unique_id']. (UNKNOWN_IDENTIFIER) (version 26.4.1.2359 (official build))\n"}
▶ clickhouse_run_select_query
{
"query": "
-- PIK3CA mutations by PAM50 subtype (patient-level)
WITH sample_to_patient AS (
SELECT DISTINCT sample_unique_id, patient_unique_id
FROM clinical_data_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
),
patient_subtypes AS (
SELECT DISTINCT patient_unique_id, attribute_value AS pam50_subtype
FROM clinical_data_derived
WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND attribute_name = 'SUBTYPE'
AND type = 'patient'
AND attribute_value != ''
),
profiled_for_pik3ca AS (
SELECT DISTINCT sp.patient_unique_id
FROM genomic_event_derived ged
JOIN sample_to_patient sp ON ged.sample_unique_id = sp.sample_unique_id
WHERE ged.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
),
pik3ca_mutated_patients AS (
SELECT DISTINCT sp.patient_unique_id
FROM genomic_event_derived ged
JOIN sample_to_patient sp ON ged.sample_unique_id = sp.sample_unique_id
WHERE ged.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND ged.hugo_gene_symbol = 'PIK3CA'
AND ged.variant_type = 'mutation'
AND ged.mutation_status != 'UNCALLED'
)
SELECT
ps.pam50_subtype,
COUNT(DISTINCT CASE WHEN pmp.patient_unique_id IS NOT NULL THEN pmp.patient_unique_id END) AS pik3ca_mutated_patients,
COUNT(DISTINCT ps.patient_unique_id) AS total_patients,
COUNT(DISTINCT CASE WHEN pp.patient_unique_id IS NOT NULL THEN pp.patient_unique_id END) AS profiled_patients,
ROUND(pik3ca_mutated_patients * 100.0 / profiled_patients, 1) AS mutation_frequency_pct
FROM patient_subtypes ps
LEFT JOIN (SELECT DISTINCT patient_unique_id FROM profiled_for_pik3ca) pp ON ps.patient_unique_id = pp.patient_unique_id
LEFT JOIN (SELECT DISTINCT patient_unique_id FROM pik3ca_mutated_patients) pmp ON ps.patient_unique_id = pmp.patient_unique_id
GROUP BY pam50_subtype
ORDER BY mutation_frequency_pct DESC
"
}
◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 1\n Code: 1. DB::Exception: Resolved identifier 'patient_unique_id' in parent scope to expression 'patient_unique_id' with correlated columns 'patient_unique_id' (Enable 'allow_experimental_correlated_subqueries' setting to allow correlated subqueries execution). In scope SELECT DISTINCT patient_unique_id FROM profiled_for_pik3ca. (UNSUPPORTED_METHOD) (version 26.4.1.2359 (official build))\n"}
▶ clickhouse_run_select_query
{
"query": "
-- PIK3CA mutations by PAM50 subtype
SELECT
cd_subtype.attribute_value AS pam50_subtype,
COUNT(DISTINCT CASE
WHEN ged.hugo_gene_symbol = 'PIK3CA' THEN cd_subtype.patient_unique_id
END) AS pik3ca_mutated_patients,
COUNT(DISTINCT cd_subtype.patient_unique_id) AS total_patients_with_subtype,
ROUND(pik3ca_mutated_patients * 100.0 / total_patients_with_subtype, 1) AS mutation_frequency_pct
FROM clinical_data_derived cd_subtype
LEFT JOIN clinical_data_derived cd_sample
ON cd_subtype.patient_unique_id = cd_sample.patient_unique_id
AND cd_subtype.cancer_study_identifier = cd_sample.cancer_study_identifier
AND cd_sample.type = 'sample'
LEFT JOIN genomic_event_derived ged
ON cd_sample.sample_unique_id = ged.sample_unique_id
AND cd_sample.cancer_study_identifier = ged.cancer_study_identifier
AND ged.hugo_gene_symbol = 'PIK3CA'
AND ged.variant_type = 'mutation'
AND ged.mutation_status != 'UNCALLED'
WHERE cd_subtype.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
AND cd_subtype.attribute_name = 'SUBTYPE'
AND cd_subtype.type = 'patient'
AND cd_subtype.attribute_value != ''
GROUP BY pam50_subtype
ORDER BY mutation_frequency_pct DESC
"
}
◀ result
{"rows":[{"pam50_subtype":"BRCA_LumA","pik3ca_mutated_patients":237,"total_patients_with_subtype":499,"mutation_frequency_pct":47.5},{"pam50_subtype":"BRCA_Her2","pik3ca_mutated_patients":26,"total_patients_with_subtype":78,"mutation_frequency_pct":33.3},{"pam50_subtype":"BRCA_LumB","pik3ca_mutated_patients":59,"total_patients_with_subtype":197,"mutation_frequency_pct":29.9},{"pam50_subtype":"BRCA_Normal","pik3ca_mutated_patients":8,"total_patients_with_subtype":36,"mutation_frequency_pct":22.2},{"pam50_subtype":"BRCA_Basal","pik3ca_mutated_patients":12,"total_patients_with_subtype":171,"mutation_frequency_pct":7}]}
═ answer (success)
Perfect! Here's the PIK3CA mutation frequency breakdown by PAM50 subtype in the TCGA PanCancer Atlas breast cancer study:
| PAM50 Subtype | PIK3CA Mutated | Total Patients | Mutation Frequency |
|---|---|---|---|
| **Luminal A** | 237 | 499 | **47.5%** |
| **HER2-enriched** | 26 | 78 | **33.3%** |
| **Luminal B** | 59 | 197 | **29.9%** |
| **Normal-like** | 8 | 36 | **22.2%** |
| **Basal-like** | 12 | 171 | **7.0%** |
PIK3CA mutations are strongly enriched in the **luminal subtypes** (especially Luminal A), consistent with the hormone receptor-positive biology of these tumors. The Basal-like subtype has the lowest PIK3CA mutation frequency at 7.0%, which aligns with its distinct molecular profile.