Group By Question
-
I'm having an issue with combining data rows returned by a query. I am working with sales data from a table joined to several other tables(confusing I know). When run the query returns multiple instances of the same company name(chain of stores), all from different areas of the country. My question is whether or not there is a way to combine all the data for one company name and be able to get totals for ie: price, # of sales, etc. I've tried using a group by statement, but as I need all the column data from the original table I'm having to group by every column name. Thanks. Cheers, Lil Turtle
-
I'm having an issue with combining data rows returned by a query. I am working with sales data from a table joined to several other tables(confusing I know). When run the query returns multiple instances of the same company name(chain of stores), all from different areas of the country. My question is whether or not there is a way to combine all the data for one company name and be able to get totals for ie: price, # of sales, etc. I've tried using a group by statement, but as I need all the column data from the original table I'm having to group by every column name. Thanks. Cheers, Lil Turtle
If you are using SQL server then the best idea is to have a view which will return all company names. Once you are done with this you can write another SQL select on view with group by clause for any further data selection. Hope this helps. Difficult - > Challenging, this simple replacement made me take my life little easy;)
-
I'm having an issue with combining data rows returned by a query. I am working with sales data from a table joined to several other tables(confusing I know). When run the query returns multiple instances of the same company name(chain of stores), all from different areas of the country. My question is whether or not there is a way to combine all the data for one company name and be able to get totals for ie: price, # of sales, etc. I've tried using a group by statement, but as I need all the column data from the original table I'm having to group by every column name. Thanks. Cheers, Lil Turtle
Lil Turtle wrote:
I am working with sales data from a table joined to several other tables(confusing I know).
Not really - Joining tables will be a regular occurrance if your database is normalised properly.
Lil Turtle wrote:
My question is whether or not there is a way to combine all the data for one company name and be able to get totals for ie: price, # of sales, etc. I've tried using a group by statement, but as I need all the column data from the original table I'm having to group by every column name.
Then what you might try to do is to perform the GROUP BY in a subquery and then join the subquery to the table which contains all the other data that you want. If you want an example, you might like to show your existing code.
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
Lil Turtle wrote:
I am working with sales data from a table joined to several other tables(confusing I know).
Not really - Joining tables will be a regular occurrance if your database is normalised properly.
Lil Turtle wrote:
My question is whether or not there is a way to combine all the data for one company name and be able to get totals for ie: price, # of sales, etc. I've tried using a group by statement, but as I need all the column data from the original table I'm having to group by every column name.
Then what you might try to do is to perform the GROUP BY in a subquery and then join the subquery to the table which contains all the other data that you want. If you want an example, you might like to show your existing code.
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
The GROUP BY in the sub query worked like a dream. Thanks for the response, I appreciate the help. A humble novice, Lil Turtle
-
If you are using SQL server then the best idea is to have a view which will return all company names. Once you are done with this you can write another SQL select on view with group by clause for any further data selection. Hope this helps. Difficult - > Challenging, this simple replacement made me take my life little easy;)
A view in this instance is not needed as the data returned is for a report that I'm doing and not part of a permanent data store. Thanks for the tip though I can apply this to other endevors.;) And I do appreciate the help, Thanks. A humble novice, Lil Turtle