Select * with group by

[UnsolvedCoding]

I want to do a select * with a gropu by clause but don't recall how at the moment.

I have the following

Select *
From Test_Table
Goupby TableName
Having DateAdded >= [2012-04-03]

How can I do this as a groupby without listing out all the columns in Test_Table?

 

[Ascalonian]

If you are grouping by all columns, I do not see any difference for using a filter.

SELECT *
  FROM Test_Table
 WHERE DateAdded >= [2012-04-03]

 

[UnsolvedCoding]

If I run a where clause I can get back a couple hundred rows per table, I only want to see the totals for each table listed.

 

[Ascalonian]

Gotcha. Well, with all the research I tried with this, everything is saying that you need to specify the columns specifically, if you want to do a group by. So I am interested in seeing if someone can actually find a solution, haha.

 

[gmmastros]

UnsolvedCoding,

Can you please show some sample data and expected results? This will make it a lot easier to help you.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

If you want to see "totals", then you have to either COUNT() or SUM,() some fields, * will not give any "totals".

You're barking up the wrong tree. If you want table totals, you don't GROUP BY at all, GROUP by will give you subtotals for each group, what you need is aggregation.

Eg to get the total cout of records SELECT COUNT(*) FROM TABLE, or to get the total sum of some amount field: SELECT SUM(amount) FROM TABLE. SELECT * won't help you, neither without or with a group by.

Also Mikey is true you can't SELECT *, even if you'd group by all fields, this will always be invalid. Even if you'd SELECT field1,field2,... FROM TABLE GROUP BY field1,field2,... what you'd get is all distinct rows, no sum, same as SELECT DISTINCT * FROM TABLE.

Once again in short: You need to aggregate.

Bye, Olaf.

 

[simian336]

In other words, if you group 3 records together how would it know which of the 3 records you want to see?

Simi

 

[r937]

In other words, if you group 3 records together how would it know which of the 3 records you want to see?

this is a poorly worded question


GROUP BY does not return any of the "records" that went into the grouping -- it returns only aggregate rows, which are not the same rows as those that were extracted from the database

aggregate rows contain data extracted from the agregation of the individual rows in the group

none of the individual rows extracted by the FROM clause are returned

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Olaf Doschke]

GROUP BY does not return any of the "records" that went into the grouping -- it returns only aggregate rows, which are not the same rows as those that were extracted from the database

Very true.

Still, if you want to aggregate ALL data of a table you can do so wihtout any GROUP BY (as I showed).

Also you can get the par of the rows you aggregate, by which you aggregate. Eg agregating amounts of a customers order you customer, SUM(amount), GROUP BY customer. The customer (his ID or name or both) can be included in the result set and come from any row of the group of rows you aggregate.

In the special case of aggregating all rows you can't have any single value of any record, you can only get count(), sum() etc. of all rows data - and that's what you want, @UnsolvedCoding, go for it.

Bye, Olaf.

 

[simian336]

Hey Rudy,

That was my point, he was asking for them to be grouped but not aggregated. So my queston to him was how was sql servers supposed to know what he wants to see.

Simi