UNION with CASE statement

Question

Post reply

UNION with CASE statement

Viewing 15 posts - 16 through 30 (of 48 total)

You must be logged in to reply to this topic. Login to reply

Newbi SSCertifiable Points: 6546 More actions · Answer 1

It will b better of with looking up UNION and UNION ALL first.

I really am not aware of importance of order in group by. Those are not intentionally order that way.

Anyway Thank you.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Newbi (6/28/2016)
It will b better of with looking up UNION and UNION ALL first.
I really am not aware of importance of order in group by. Those are not intentionally order that way.
Anyway Thank you.

Itemcode and ItemName are switched in the unioned queries.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Newbi SSCertifiable Points: 6546 More actions · Answer 3

Finally I can ,sort of , get it work. But not exactly what I want, yet.

When I run the query , I am getting the duplicate of subtotal by Products. If there are two items with same ItemCode, I want to have combined Subtotal, instead of getting separately. As an instance, at line 3 and 4, there should be one subtotal at line 5 with total GrossWt. But I am getting two results for this. I would rather want one subtotal at line5 with result 1.008 GrossWt. How can I make it to achieve what I want? Please show me the way. Thanks

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

Its always better to share the query in text instead of a image it make things a lot easier for the folks.

Following will achieve the result:

SELECT

Zone, ItemCode, [Products], GrossWt

FROM

(

SELECT

Zone, ItemCode,ItemName as [Products], GrossWeight AS GrossWt

FROM

PickLists_Details

UNION ALL

SELECT

NULL AS Zone, ItemCode, 'Subtotal' AS ItemName, SUM(GrossWeight) AS GrossWt

FROM

PickLists_Details

GROUP BY

ItemCode

) source

ORDER BY

ItemCode, (CASE WHEN [Products] = 'Subtotal' then 1 else 0 END), [Products]

Hope it helps.

Newbi SSCertifiable Points: 6546 More actions · Answer 5

Thank you very much. Now it is resulting exactly what I want.:-):-):-)

Newbi SSCertifiable Points: 6546 More actions · Answer 6

hi again,

How can I make it sort-by Zone, I mean I want to have subtotal of each item, but wanna order ascending by Zone.

As in,

001/1 xxx1 DEF-3 0.504 1

-null- -null- Subtotal 0.504 1

001/1 xxx4 YKK-20 3.100 2

-null- -null- Subtotal 3.100 2

...

002/1 xxx1 ABC-2 0.756 1

-null- -null- Subtotal 0.756 1

002/1 xxx2 DEF-3 0.504 1

-null- -null- Subtotal 0.504 1

etc...

(if there are two items under xxx1, it will have to be like:

001/1 xxx1 DEF-3 0.504 1

-null- -null- DEF-4 0.200 2

-null- -null- Subtotal 0.704 3

...)

How to write the query for that ? I still am having incorrect query result.

Thanks

Umer Akram SSC-Insane Points: 22209 More actions · Answer 7

(if there are two items under xxx1, it will have to be like:
001/1 xxx1 DEF-3 0.504 1
-null- -null- DEF-4 0.200 2
-null- -null- Subtotal 0.704 3
...)

What do mean by this? need to share some detail. and please share you Sample Data as well Your Desired output in a proper way as mentioned in the earlier.

Please read How to post data/code on a forum to get the best help[/url] before sharing any further details. Thanks

How to write the query for that ? I still am having incorrect query result.

Whats was wrong the solution provided earlier?? What have you done so far to be able to get this result.

Newbi SSCertifiable Points: 6546 More actions · Answer 8

Your result is giving me total of item and gross wt. I was applying inside my SSRS and cannot sort-by Zone, that's why I want to make a sort by Zone in SQL. I tried to play around with the query you have provided, but my query is going crazy. Actually what I did was I just try to group-by with Zone in those queries,inner and outer. I don't know what function to use inorder to achieve my requirement.

SELECT Zone, ItemCode, [Products], GrossWt,Quantity FROM

( SELECT Zone, ItemCode,ItemName as [Products], GrossWeight AS GrossWt,Quantity FROM PickLists_Details

UNION ALL

SELECT NULL AS Zone, ItemCode, 'Subtotal' AS ItemName, SUM(GrossWeight) AS GrossWt,SUM(Quantity) AS TotalQ

FROM PickLists_Details

GROUP BY ItemCode) source

ORDER BY ItemCode, (CASE WHEN [Products] = 'Subtotal' then 1 else 0 END), [Products]

I just added in one more column to your given script.

The result that I wanna get(to be able to apply well for SSRS) is as follow:

--001/1 xxx1 DEF-3 0.504 1

---null- -null- Subtotal 0.504 1

--001/1 xxx4 YKK-20 3.100 2

---null- -null- Subtotal 3.100 2

--...

--002/1 xxx1 ABC-2 0.756 1

---null- -null- Subtotal 0.756 1

--002/1 xxx2 DEF-3 0.504 1

---null- -null- Subtotal 0.504 1

--etc...

--(if there are two items under xxx1, it will have to be like:

--001/1 xxx1 DEF-3 0.504 1

---null- -null- DEF-4 0.200 2

---null- -null- Subtotal 0.704

I have attached the result image of your first query.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 9

Newbi (6/30/2016)
Your result is giving me total of item and gross wt. I was applying inside my SSRS and cannot sort-by Zone.

My question would be, if you are working on SSRS, why you even need a subtotal in a query ??? Its a fairly basic requirement in a report you can achieve it in the SSRS very easily so why doing it in query ????

Newbi SSCertifiable Points: 6546 More actions · Answer 10

Because SSRS designing alone cannot fulfill my requirements. Of course I will be more than happy if I can design there to meet my report requirements. That's why I am thinking the alternatives. I have been struggling with that problem many weeks already :rolleyes:

Umer Akram SSC-Insane Points: 22209 More actions · Answer 11

Newbi (6/30/2016)
Because SSRS designing alone cannot fulfill my requirements.

Shed some light on it.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 12

twin.devil (6/30/2016)
Newbi (6/30/2016)
Because SSRS designing alone cannot fulfill my requirements.
Shed some light on it.

and please post some sample data so that we can all work on the same data set (see my earlier post on this thread for an example)

thanks

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Newbi SSCertifiable Points: 6546 More actions · Answer 13

I need to have [1] 2 groupings (group by zone and itemcode), [2] Totals( subtotal by each itemCode, and Grand total at the end of the tablix), [3]custom page break for every page, (the purpose is to maintain the same length of the tablix),[4] add blank row(s) if the data-driven tablix is shorter compared to the previous pages'.

🙁

Umer Akram SSC-Insane Points: 22209 More actions · Answer 14

Newbi (6/30/2016)
I need to have [1] 2 groupings (group by zone and itemcode), [2] Totals( subtotal by each itemCode, and Grand total at the end of the tablix), [3]custom page break for every page, (the purpose is to maintain the same length of the tablix),[4] add blank row(s) if the data-driven tablix is shorter compared to the previous pages'.
🙁

Every single point you have mentioned, none of them related to t-sql, All this functionality is done Should be done at report level.

Irrespective of which reporting tool (for your case SSRS).

Hope it helps.

Newbi SSCertifiable Points: 6546 More actions · Answer 15

I don't know how to display well enough here according to the guidelines of the forum. :crying: