Question SQL Syntax - UNION + GROUP BY + ORDER BY

[thebatfink]

Hi, having an absolute brick wall today and seemingly unable to work anything out alone :/

I want to perform a union query, and then group and order the results. I have taken care to make sure the same number of fields are present from both select queries and all the field names match etc, but I still can't seem to make it work. I'm having a hard time formatting a google search string that works to find examples.

Here is the bones of it.. but obviously I have some dodgy syntax as it doesn't like it!

SELECT CONVERT(nvarchar(10), MyersTable.PlanWeek, 103) as [PlanWeek], IntIngredientTable.IngredientName, SUM(IntIngredientTable.Quantity) as [Quantity (KG)]
FROM IntIngredientTable
INNER JOIN MyersTable on IntIngredientTable.MyersID = MyersTable.MyersID
UNION ALL
SELECT CONVERT(nvarchar(10), RawDataTable.PlanWeek, 103) as [PlanWeek], IngredientTable.IngredientName, SUM(IngredientTable.Quantity) as [Quantity (KG)]
FROM IngredientTable
INNER JOIN RawDataTable on IngredientTable.RawDataID = RawDataTable.RawDataID
WHERE IngredientTable.Intermediate='No'
GROUP BY IngredientName, PlanWeek
ORDER BY CONVERT(DateTime, PlanWeek, 103)

What am I doing wrong? Thanks!

 

[Herman]

What is it exactly that is not working?

 

[InertiaM]

SELECT CONVERT(nvarchar(10), MyersTable.PlanWeek, 103) as [PlanWeek], IntIngredientTable.IngredientName, SUM(IntIngredientTable.Quantity) as [Quantity (KG)]
FROM IntIngredientTable
INNER JOIN MyersTable on IntIngredientTable.MyersID = MyersTable.MyersID
[COLOR="#FF0000"]GROUP BY IngredientName, PlanWeek[/COLOR]

UNION ALL

SELECT CONVERT(nvarchar(10), RawDataTable.PlanWeek, 103) as [PlanWeek], IngredientTable.IngredientName, SUM(IngredientTable.Quantity) as [Quantity (KG)]
FROM IngredientTable
INNER JOIN RawDataTable on IngredientTable.RawDataID = RawDataTable.RawDataID
WHERE IngredientTable.Intermediate='No'
GROUP BY IngredientName, PlanWeek

ORDER BY CONVERT(DateTime, PlanWeek, 103)

 

[thebatfink]

SELECT CONVERT(nvarchar(10), MyersTable.PlanWeek, 103) as [PlanWeek], IntIngredientTable.IngredientName, SUM(IntIngredientTable.Quantity) as [Quantity (KG)]
FROM IntIngredientTable
INNER JOIN MyersTable on IntIngredientTable.MyersID = MyersTable.MyersID
[COLOR=#ff0000]GROUP BY IngredientName, PlanWeek[/COLOR]

UNION ALL

SELECT CONVERT(nvarchar(10), RawDataTable.PlanWeek, 103) as [PlanWeek], IngredientTable.IngredientName, SUM(IngredientTable.Quantity) as [Quantity (KG)]
FROM IngredientTable
INNER JOIN RawDataTable on IngredientTable.RawDataID = RawDataTable.RawDataID
WHERE IngredientTable.Intermediate='No'
GROUP BY IngredientName, PlanWeek

ORDER BY CONVERT(DateTime, PlanWeek, 103)

Good morning, thanks for the reply. Using the above SQL I get an exception error at runtime:

ORDER BY items must appear in the select list if the statement contains a UNION.

I'm confused :/ Also I suppose its worth mentioning at this point that I am working with an SQLCE local database, incase that is preventing this working?

Thanks.

EDIT:

Ok, so I have had a play and these are now my issues!

Problem 1: It doesn't like ordering with 'CONVERT(DateTime, PlanWeek, 103)' but is fine with 'PlanWeek'
Problem 2: I need to group both result sets from the two SELECT queries. What I have now is grouping each individual SELECT.

 

[thebatfink]

I think I solved the grouping issue with a DERIVED GROUP BY. It looks to give me what I want?

I would like to understand why the ORDER BY isn't working correctly though with the convert function in it. Thanks!

SELECT PlanWeek, IngredientName, SUM([Quantity (KG)])
FROM
(
SELECT CONVERT(nvarchar(10), MyersTable.PlanWeek, 103) as [PlanWeek], IntIngredientTable.IngredientName, SUM(IntIngredientTable.Quantity) as [Quantity (KG)]
FROM IntIngredientTable
INNER JOIN MyersTable on IntIngredientTable.MyersID = MyersTable.MyersID
GROUP BY IngredientName, PlanWeek
UNION ALL
SELECT CONVERT(nvarchar(10), RawDataTable.PlanWeek, 103) as [PlanWeek], IngredientTable.IngredientName, SUM(IngredientTable.Quantity) as [Quantity (KG)]
FROM IngredientTable " & _
INNER JOIN RawDataTable on IngredientTable.RawDataID = RawDataTable.RawDataID " & _
WHERE IngredientTable.Intermediate='No'
GROUP BY IngredientName, PlanWeek
)
DERIVED GROUP BY IngredientName, PlanWeek
ORDER BY PlanWeek

 

[InertiaM]

CONVERT(nvarchar(10), MyersTable.PlanWeek, 103)

and

CONVERT(DATETIME, RawDataTable.PlanWeek, 103)

are not the same - this is why it was failing. I believe that the ORDER BY statement needs to EXACTLY match one of the fields in your SELECT statement.

 

[Herman]

Actually they just need to be "similar" datatypes according to MSDN. So DATE or TIME vs DATETIME would work, but not nvarchar.

 

[cjard]

You should have named your columns, then put the names in the orderby

SELECT function(blah) as MyCOlName FROM tables ORDER BY blah


Here's how I think of queries:

TABLE:
a block of data

SELECT:
a block of data

JOINING:
makes a block of data wider

UNIONING:
makes a block of data taller

GROUPING:
gathers equal values and hides repeated rows

WHERE:
restricts a block of data after it comes out

ON:
just like where, but specified at the time the join is being done

HAVING:
restrict a block after it has been group byed

ORDER BY:
put rows in order AFTER EVERYTHING else, but before it's shown to the user


-

In a union query only the first query needs to specify column heads:

SELECT 1 as NumberCol, 'a' as TextCol
UNION
SELECT 2, 'b'

All columns from all queries need to share a datatype or be implicitly convertible because all data in a column is ofthe same data type

This would then be ordered by:

ORDER BY Numbercol ASC, TextCol DESC


-
i.e. the database engine has run the quiery,assembled the results, put headers to them, and is then ordering the results as a separate instruction

-

Any block of data can be used as a source to another SELECT block of data:

SELECT col FROM table

SELECT col1 FROM (SELECT col1, col2 FROM table)

SELECT col1, max(col2) as maxCol2 FROM (SELECT col1, col2 FROM table GROUP BY col, col2) INNER JOIN (SELECT 1 as col3 UNION SELECT 4 as col3) ON col1 = col3 GROUP BY col1 HAVING col1 like "hello%" ORDER BY maxCOl2

-

There is no big mystery to SQL; it's all just blocks of data that are rectangular, and connect to other blocks either horizontally or vertically, with some bits filtered out or having math done on them

 

[cjard]

incidentally, you should indent your SQLs like this (in my opinion) because you can infer rules from the indent level, like everything at the same level is part of the same operation (to make wider, taller, filter, group etc)

SELECT
  columns
FROM
  table
  INNER JOIN
  othertable
  ON
    cola=colb

  INNER JOIN
  (
    SELECT
      morecols
    FROM
      moretables
    GROUP BY
      blahblah
  ) somealias
  ON
   somealias.somecol=outercolumn
WHERE
  cola=value AND
  colb=othervalue
GROUP BY
  somecol
ORDER BY
  anothercol

This lets you read vertically, see nested selects, see how they connect and at what level of the query execution, and really helps you see where data starts, and flows (from deep nest/indents out to shallowest nest/indent levels


And always give everything an alias, especially table names