General SQL Questions (Group by, Select Case)

[joacker]

Hello guys, i have 3 questions concerning sql-select statements.

1)
Suppose i have a group by select statement, then i need for every column that is not used in group by an aggregate function:

select
id
min(date)
min(name)
...
FROM
table
GROUP BY id

Is there any way to avoid all this aggregate functions?

2)
afaik i cann't use the column aliases in the same select case:

select
convert(int,(a+b)) as id
convert(int,b) as name
case
When id = 20 THEN 1
...
End

Is there any way i can use an aliase here instead of convert(int,(a+b)) all the time?

3)
Suppose i have the following table:
id | val_a | val_b
1 | 10 | NULL
2 | 29 | 92
3 | 39 | NULL

Now i am asking for a select statement that counts all the rows and also counts the val_b rpws wjere val_b = NULL. Do i have to work around with count(*) - count(val_b)?

Thx for your advices

 

[RiverGuy]

1. You will have to code the aggregates for each.

2. A way to get around that would be use a subquery.

SELECT z.*,
CASE
  WHEN id = 20 THEN 1
  ...
END
FROM
(SELECT  convert(int,(a+b)) as id,
convert(int,b) as name
FROM YourTable) z

3. That would be one way to do it.

 

[gmmastros]

1. You can use a derived table to accomplish what you want. this allows you to group by ONLY those columns that it makes sense to group on.

Ex:

Select Table.Id,
       Table.Name,
       DerivedTableAlias.MinDate
From   Table
       Inner Join (
         select id,
                min(date) As MinDate
         FROM table
         GROUP BY id
         ) As DerivedTableAlias
         On Table.Id = DerivedTableAlias.Id
         And Table.Date = DerivedTableAlias.Date

2. You cannot use alias the way you want, but there is a way. If this expression is something you use a lot, you could make a computed column in the table. Like this:

Alter Table TableName Add Id As convert(int,(a+b))

Now, when you select the ID column from the table, it will be there for you. You cannot update the value of a computed column, but every time you use it, it will use the calculation that you give it.

3. That is going to be the most efficient way to do it. There are other ways, but I don't necessarily think it's any better. For example:

Select Sum(Case When Val_B Is NULL Then 1 Else 0 End) As CountOfNulls

I hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[joacker]

Thank you guys. 2 helpful answers after 30 min - your support is awesome

 

[gmmastros]

I was just thinking about #3 a little more. You could also add another computed column to the table which will allow you to get the count of NULLs. Like this...

Alter Table YourTableName Add ValBIsNull As Case When Val_B Is NULL Then 1 Else NULL End

Then, to count the number of null's, you could...

[tt][blue]Select Count(ValBIsNull) As CountOfNulls[/blue][/tt]

You should understand that computed columns are no more efficient than writing the query without it (the long way), but in some cases, it makes the code more readable. Personally, I prefer to write the extra code because it is clearer to me what the query is doing. Also, with lots of computed columns, you may have a difficult time remembering which columns in the table are real, and which are computed.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

I'm a little confused on #1 now. Were you wanting to specify columns to group on, and then apply the MIN aggregate to all other columns, or were you wanting to specify just a couple aggregates and select the rest of the columns?

 

[joacker]

@RiverGuy: As i am grouping by ID i have to choose an aggregate function for all the rows in the select-statement. Therefore i choose min(x) but the most values are the same for the grouped ids. (this means name = name for the grouped ids so i can take min() or max()).

So i have 2-3 "real" aggregate functions that really aggregate a value and the rest i just use to avoid the error.