How to query data base with 3 different conditions

[VishwaKL]

Hi Guys, I have a condition that, I have to display data for all states with 3 different conditions like i have a state column and i have to calculate count for judicial, non-judicial , and redemption these columns i have to display for each state how can i make it happen in a single query with good performance. I have 3 different conditions to get count, so please help me ex

            State
        

        

            Judicial
        

        

            Non-Judicial
        

        

            Redumtion
        

    

    

        

            AZ
        

        

            10
        

        

            01
        

        

            0
        

    

    

        

            CA
        

        

            12
        

        

            94
        

        

            04
        

    

    

        

            NV
        

        

            32
        

        

            04
        

        

            04
        

    

    

        

            QA
        

        

            32
        

        

            49
        

        

            04

[Lost User]

Fix your formatting. What is your table structure? In general, you should be able to do that using GROUP BY clause and SUM() aggregate function.

[Richard Deeming]

As Shameel said, something like this should work:

SELECT
State,
Sum(Case When TheColumn = 'Judicial' Then 1 Else 0 End) As JudicialCount,
Sum(Case When TheColumn = 'Non-Judicial' Then 1 Else 0 End) As NonJudicialCount,
Sum(Case When TheColumn = 'Redemption' Then 1 Else 0 End) As RedemptionCount
FROM
YourTable
GROUP BY
State

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Lost User]

Welcome to the database forum; as you have noticed, it focuses on SQL and databases. Are you by any chance looking for an XPath-query? :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

[VishwaKL]

Thank you Richard Deeming. i written query like what you have written, is this style of query takes less execution time ?

[Richard Deeming]

VishwaKL wrote:

is this style of query takes less execution time ?

Less than what? You'd need to measure the performance of this query against the query you want to compare it to.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer