Union query Problem.

• C-227852

  Grasshopper

  Points: 14

  More actions

  April 30, 2005 at 10:20 am

  #166892

  I made a qury that is the sum of another query. So for example my total query([QryTotal]) comes out like this

  Number_of_Shipments, Weight, Charge, Additional_Costs

  80 60 100 70

  But my boss wants these figures in one coloumn

  So I made a union query.

  Select [Number_of_Shipments] from [QryTotal];

  Union

  Select [Weight] from [QryTotal];

  Union

  Select [Charge] from [QryTotal];

  Union

  Select [Additional_Costs] from [QryTotal];

  The Result I get is

  60

  70

  100

  80

  Something like this (the order varies every time I make a new query). I want to get the results in the order of sequence I typed. For example

  80

  60

  100

  70

  Can anyone help me with this query please?

• Kathi Kellenberger

  SSChampion

  Points: 11811

  More actions

  April 30, 2005 at 8:11 pm

  #556160

  Try this:

  Select [Number_of_Shipments],1 as sortORder from [QryTotal];

  Union

  Select [Weight],2 from [QryTotal];

  Union

  Select [Charge],3 from [QryTotal];

  Union

  Select [Additional_Costs],4 from [QryTotal]

  order by sortOrder;

  The bad thing is that now you have an additional column. You could save this query and then select only your total column from the saved query.

  Aunt Kathi Data Platform MVP
  Author of Expert T-SQL Window Functions
  Simple-Talk Editor

• C-227852

  Grasshopper

  Points: 14

  More actions

  May 1, 2005 at 12:38 am

  #556164

  Hi Kathi,

  Thank you so much, this works.