Group By Issue

I was asked to display two fields plus a total counts of the table but only need to group by one field and leave the other field as Display field. For example:

Select A, B, Count(*)
From Table
where ...
Group By B

It won't work on SQL unless you group both fields. Any way to get around this?

Thanks!

J827Think about it, and you'll see that it makes no logical sense.

If value A remains constant for any value B, then go ahead and group by A, as it will not affect your output.

Select A, B, Count(*)
From Table
where ...
Group By A, B

If value A varies for any given value B, then which value are you going to show in your output? You need some type of criteria for deciding. You could, for instance, use the lowest value of A:

Select min(A) as A, B, Count(*)
From Table
where ...
Group By B

I think you need to better define, or at least better explain, what your objective is.|||blindman,

Thank you for your quick response!

The value A actually is coming from different table and it is not a constant for any value B. If not grouping B, they are just part of combination running results based on the business logic but my business partner would like to display both fields in the report for no calculations should be run off of A field. Is this feasible or not?

J827|||Calculate your B totals in a subquery:

select TableA.A, Subquery.B, Subquery.RecordCount
from TableA
left outer join (select B, count(*) as RecordCount from TableB group by B) Subquery
on TableA.B = Subquery.B|||GROUP BY can return one row for each column you specify in the GROUP BY clause, plus any additional aggregates of that group as a column.

So if you have TableA, with columns Title, Sales, Price, a valid GROUP BY would be:

SELECT Title, SUM(Sales) AS Sales, MAX(PRICE) AS MaxPrice, (SUM(SALES) * (SUM(PRICE)) AS Total FROM TableA GROUP BY Title

If the values for ValueB are constant with respect to a specfic value of ValueA, then you can kinda fudge this by using a MIN or MAX function. In which case you don't need to include the column in the GROUP BY clause because MIN and MAX are aggregate functions.