conditional-statementsmaxdistinct# Query Statement: Using DISTINCT but also using MAX condition

I have a table in my db which is minimally represented below. Note that there are entries under PRV that are duplicates.

PRV | MV | AU |
---|---|---|

G-111 | 500 | LR |

G-112 | 400 | LC |

G-112 | 401 | LA |

G-200 | 650 | LI |

G-115 | 350 | LR |

G-115 | 0 | LC |

G-115 | 0 | LA |

G-116 | 0 | LR |

I used the following query statement to get a count of the number of distinct PRV grouped according to AU.

```
SELECT
COUNT(*),
AU
FROM
(
SELECT DISTINCT
PRV,
AU
FROM
tablename
WHERE
MV > O
) AS K
GROUP BY
AU
```

RESULT:

`COUNT(*)` |
AU |
---|---|

2 | LR |

1 | LC |

0 | LA |

1 | LI |

What I can't figure out is how to make sure that the grouping is not just based on `MV>0`

but also on `MV`

being the max among duplicate `PRVs`

.

For the last 3 rows (where `PRV = 'G-115'`

), my query statement works alright because the row with
AU = LR has MV=350 while the others have zero. Thus, G-115 is counted as just one unit and grouped under LR.

But, in the case of the 2nd and 3rd row (where PRV = 'G-112'), it is indeed counted as just 1 (because of the DISTINCT keyword), but I want it to be grouped as LA (not LC) because LA's MV = 401 (which is greater than MV=400 in the LC row).

So, the desired result would be:

`COUNT(*)` |
AU |
---|---|

2 | LR |

0 | LC |

1 | LA |

1 | LI |

I read about `MAX`

keyword but I can't figure out how to use it in my case.

In my actual table, the `distinct`

keyword doesn't seem to work for rows 2 and 3. They are actually counted twice because `MV > 0`

for both entries.

Solution

```
SELECT
m.AU,
COUNT(x.MV)
FROM mytable m
LEFT JOIN (
SELECT
PRV,
MAX(MV) as MV
FROM mytable
GROUP BY PRV) x ON x.PRV = m.PRV and x.MV = m.MV
WHERE m.MV > 0
GROUP BY m.AU;
```

see: DBFIDDLE

The left join, to the subquery which calculates the MAX value for MV does make sure you do not count the LC value, because it does not have the MAX value.

output:

AU | COUNT(x.MV) |
---|---|

LR | 2 |

LC | 0 |

LA | 1 |

LI | 1 |

- BigQuery SQL: Conditionally Count Column Values (if exactly 2/3 codes exist from list) While Grouping By Unique ID
- BigQuery SQL: Conditionally Count Column Values (if only one code exists from list) While Grouping By Unique ID
- Conditional redirect to mobile subdomain
- How do I check truthy when using Ternary Operator
- How do I return True if a string in a cell is contained anywhere in a column?
- SQL Oracle conditional group by
- How do I copy certain cells from one sheet to another conditional on the cell value in both sheets?
- Grouped conditions on grouped data [R]
- Using EXCEL to find shaded cells, and shading other cells if found
- Python pandas - Adding a list to a dataframe cell if multiple conditions are met
- Copying rows to another workbook based on if a cell holds a numeric value / condition: isnumeric = true
- Adding OR condition to an array in google sheets
- Creating a conditional column in DAX based on 3 other columns
- Simpler way to check if variable is not equal to multiple string values?
- Conditional rendering else statement does not apply the styles to my component
- WPF XAML DataGridTemplateColumn assign to two DataTemplate with conditional types
- How can I Select which Environment to run from a single stage in an Azure Pipeline with conditionals or another way
- Display WooCommerce Product Availability Conditionally
- How to make a function inside a loop independent of the condition?
- Understanding a weird SQL clause
- How to flag occurrences on multiple conditions in Teradata
- Python pandas, filter our data with isin and other conditions
- Python Conditional Variable Setting
- Excel Formula to highlight cell(s) in the same column based on time difference
- Use Table Header as Content In a Cell
- Terraform conditional argument block
- Pandas replace with default value
- Pandas - Case when & default in pandas
- Adding a new column by comparing values from multiple data frames and conditions in R
- Automatically filter data by two conditions