2

Take the following example:

SELECT * FROM [Orders] WHERE DATE = '2021-09-10'

Date        FirstName  LastName   Adults  Item       Description
----------  ---------  ---------  ------  ---------  --------------
2021-09-10  David      Smith      3       Breakfast  Eggs and beans
2021-09-10  Jennifer   Ford       2       Breakfast  Eggs and beans
2021-09-10  Thomas     Oliver     1       Breakfast  Eggs and beans
2021-09-10  Lisa       Parker     2       Breakfast  Continental
2021-09-10  David      Smith      3       Dinner     Steak
2021-09-10  Tom        Haverford  1       Lunch      Hamburger
2021-09-10  Reanu      Keeves     2       Dinner     Steak

.. I need to group and sum so that all the distinct [Breakfast] items combine as one row per; summing the [Adults], while the rest output as separate records:

Date        FirstName  LastName   Adults  Item       Description
----------  ---------  ---------  ------  ---------  --------------
2021-09-10                        6       Breakfast  Eggs and beans
2021-09-10                        2       Breakfast  Continental
2021-09-10  David      Smith      3       Dinner     Steak
2021-09-10  Tom        Haverford  1       Lunch      Hamburger
2021-09-10  Reanu      Keeves     2       Dinner     Steak

Is there a handy way to build this as a single query?

4

You don't need UNION, you can do this in one query.

It's just a matter of picking your grouping values carefully:

SELECT
    [Date],
    FirstName = CASE WHEN Item <> 'Breakfast' THEN FirstName END,
    LastName  = CASE WHEN Item <> 'Breakfast' THEN LastName  END,
    Adults = SUM(Adults),
    Item,
    Description
FROM
    [Orders]
WHERE
    [Date] = '2021-09-10'
GROUP BY
    [Date],
    Item,
    Description,
    CASE WHEN Item <> 'Breakfast' THEN FirstName END,
    CASE WHEN Item <> 'Breakfast' THEN LastName END;

db<>fiddle

0
2

You can UNION two queries in this way:

SELECT
    [Date],
    '' as FirstName,
    '' as LastName,
    SUM(Adults) as Adults,
    Item,
    Description
FROM
    [Orders]
GROUP BY
    [Date],
    Item,
    Description
HAVING
    [Date] = '2021-09-10'
    AND Item = 'Breakfast'

UNION

SELECT
    [Date],
    FirstName,
    LastName,
    Adults,
    Item,
    Description
FROM
    [Orders]
WHERE
    [Date] = '2021-09-10'
    AND Item <> 'Breakfast'    

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.