SQL - Where Aggregate>1

SQL aggregate function when count(*)=1 so there can be only one value

You could, instead, use a windowed COUNT and then filter based on that:

WITH CTE AS(
    SELECT ba.book_id,
           ba.author_id,
           COUNT(ba.book_id) OVER (PARTITION BY ba.book_id) AS Authors
    FROM dbo.book_authors ba)
SELECT c.book_id,
       c.author_id
FROM CTE c
WHERE c.Authors = 1;

An alternative method would be to use a correlated subquery:

SELECT ba.book_id,
       ba.author_id
FROM dbo.book_authors ba
WHERE EXISTS (SELECT 1
              FROM dbo.book_authors e
              WHERE e.book_id = ba.book_id
              GROUP BY e.book_id
              HAVING COUNT(*) = 1);

I have not tested performance on either with a decent amount of data, however, I would hope that for a correlated subquery with a well indexed table, you should see better performance.

SQL Aggregate function based on 1 column and display the remaining

You can use a SUM window function:

Test data:

declare @t table (Column1 varchar(10), Column2 varchar(10), Column3 varchar(10), Column4 int)
insert into @t values 
('Col1Data0','Test1','Test2',1),
('Col1Data0','Test1','Test2',3),
('Col1Data1','Test1','Test2',2),
('Col1Data1','Test1','Test2',5)

Query:

SELECT Column1, Column2, Column3, 
    SUM(Column4) OVER (PARTITION BY Column1) AS SumColumn4
FROM @t

Returns:

Column1     Column2 Column3 SumColumn4
Col1Data0   Test1   Test2   4
Col1Data0   Test1   Test2   4
Col1Data1   Test1   Test2   7
Col1Data1   Test1   Test2   7

Select top 1 row with aggregate function

Assuming this is SQL Server try this :

    Select t.ID, t.Name, team.TeamName, count(t.TeamName) countt
from @temp t join
(Select id, TeamName, Row_Number() over (Partition By ID Order By TeamName asc) as rn
from @temp) team on (team.ID = t.ID and team.rn=1)
Group by t.ID, t.Name, team.TeamName

SQL - WHERE AGGREGATE1

You want the having clause, like so:

select 
    firstname,
    count(*) 
from Customers 
group by firstname
having count(*) > 1
order by 1

SELECT Top(1), aggregate function with Where clause

It sounds like all of the items in the table need to be summed and returned with the max identifier. Would this work for you?

Select Max(ID), Sum(Number) from TableName

ID would be your Unique Identifier column name.
Number would be your column name that holds the numbers.
TableName is the name of your table.

SQL aggregate get monthly and yearly

The rows for the whole years contain the NULL value in the month column

SELECT MONTH(date) AS Month,
       YEAR(date) AS Year,
       currency AS Currency,
       SUM(amount) AS Total
FROM invoices 
GROUP BY MONTH(date), YEAR(date), currency
UNION ALL
SELECT NULL, YEAR(date), currency, SUM(amount)
FROM invoices 
GROUP BY YEAR(date), currency
ORDER BY Year, Month IS NULL, Month

You can find a demo here

EDIT: ordered the result set as for requirement

EDIT2: I still don't understand why the solution was not acceptable before, I tweaked it a bit to make it coincide with the new prototype of the result

SQL aggregate function to return single value if there is only one, otherwise null

Will this work for for your original data?

SELECT  ID, 
        CASE WHEN COUNT(DISTINCT VAL) = 1 AND COUNT(ID) = COUNT(VAL)
             THEN MAX(VAL)
             ELSE NULL
        END ONLY_VAL
FROM    EXAMPLE
GROUP   BY ID
ORDER   BY ID

SQLFiddle Demo