Query That Counts Records With A Where Clause

SELECT EmailOfConsumer, COUNT(EmailOfConsumer) as 'NumberOfOrders', SUM(CAST(Total as money)) as 'TotalValue',

Solution 1:

use

HAVINGCOUNT(EmailOfConsumer) =1

The having clause restricts a aggregate whereas the where clause only put restrictions on individual column data

Solution 2:

Using group by and then having clause. Refer this

SELECT 
            EmailOfConsumer, 
            COUNT(EmailOfConsumer) as'NumberOfOrders',
            SUM(CAST(Total as money)) as'TotalValue',
            (SUM(CAST(Total as money))/COUNT(EmailOfConsumer)) as'AverageValue'FROM webshop
            GROUPBY EmailOfConsumer 
            HAVINGCOUNT(EmailOfConsumer) ='1'ORDERBY TotalValue DESC

Solution 3:

SELECT 
                EmailOfConsumer, 
                COUNT(EmailOfConsumer) as'NumberOfOrders',
                SUM(CAST(Total as money)) as'TotalValue',
                (SUM(CAST(Total as money))/COUNT(EmailOfConsumer)) as'AverageValue'FROM webshop
                GROUPBY EmailOfConsumer 

HAVINGCOUNT(EmailOfConsumer) ='1'ORDERBY TotalValue DESC

Solution 4:

You can try

SELECT 
            EmailOfConsumer, 
            COUNT(EmailOfConsumer) as'NumberOfOrders',
            SUM(CAST(Total as money)) as'TotalValue',
            (SUM(CAST(Total as money))/COUNT(EmailOfConsumer)) as'AverageValue'FROM webshop
            GROUPBY EmailOfConsumer 
            HAVINGCOUNT(EmailOfConsumer) =1ORDERBY TotalValue DESC

Solution 5:

Use this query:

SELECT 
    EmailOfConsumer, 
    COUNT(EmailOfConsumer) as'NumberOfOrders',
    SUM(CAST(Total as money)) as'TotalValue',
    (SUM(CAST(Total as money))/COUNT(EmailOfConsumer)) as'AverageValue'FROM webshop
GROUPBY EmailOfConsumer 
HAVINGCOUNT(*) =1ORDERBY TotalValue DESC

Notes:

• WHERE clause precedesGROUP BY, while HAVING clause followsGROUP BY
• WHERE clause filters rows before aggregation; HAVING clause filters aggregated rows
• COUNT(...) returns a number, so the constant 1 should not be enclosed in quotes.