Union & group by Q

15 Dec 2005 3:36 PM

__Stephen

I am normalizing a sales header file for a cross tab report. I want to make
a column that will describe the total in the next column.

Sales, Shipping, Tax

In my select statement I define the text as a column like this:
'Sales' as Type,
Sum(Sales) Amount

but my group by won't take either
,'Sales'

or ,type

What should I change to get the grouping to work properly?

TIA

__Stephen

15 Dec 2005 3:50 PM

David Portas

__Stephen wrote:
Show quote

> I am normalizing a sales header file for a cross tab report. I want to make
> a column that will describe the total in the next column.
>
> Sales, Shipping, Tax
>
> In my select statement I define the text as a column like this:
> 'Sales' as Type,
> Sum(Sales) Amount
>
> but my group by won't take either
> ,'Sales'
>
> or ,type
>
>
> What should I change to get the grouping to work properly?
>
> TIA
>
> __Stephen

You don't need either 'Sales' or Type in the GROUP BY because that
column is a constant. You haven't shown us what your whole query looks
like but I'd guess it will be something like this:

SELECT x, 'Sales' AS type, SUM(sales) AS amount
FROM your_table
GROUP BY x ;

--
David Portas
SQL Server MVP
--

15 Dec 2005 4:04 PM

__Stephen

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1134661827.275728.321420@z14g2000cwz.googlegroups.com...

>
> You don't need either 'Sales' or Type in the GROUP BY because that
> column is a constant. You haven't shown us what your whole query looks
> like but I'd guess it will be something like this:
>
> SELECT x, 'Sales' AS type, SUM(sales) AS amount
> FROM your_table
> GROUP BY x ;

Perfect! I don't need it. Thanks.

On the pretty side it's like this:

SELECT x,
'Sales' AS type,
SUM(sales) AS amount
FROM your_table
GROUP BY x

union
SELECT x,
'Shipping' AS type,
SUM(shiping + addlShipping) AS amount
FROM your_table
GROUP BY x

uinon
SELECT x,
'Tax' AS type,
SUM(SalesTax) AS amount
FROM your_table
GROUP BY x

order by 1,2