Samar avatar image
Samar asked

Adding tbale with 2 columns having one as LOV

Hi, I have written script for getting the count for a specific TAB, but i need 2 column and count for them. Pls help on how to write it? current script: SELECT order_status, COUNT(order_status) FROM GDC WHERE ((ORDER_STATUS NOT LIKE 'Provisioning-Order Eliminated' AND ORDER_STATUS NOT LIKE 'Provisioning-Order Completed' AND ORDER_STATUS NOT LIKE 'Provisioning-Order Returned')) GROUP BY order_status ORDER BY COUNT(order_status) DESC additional reuqired is NIMS Status- this is actually a LOV and has 2 states issued and pending. t-sql edit|more ▼ asked yesterday Samar 1 edited yesterday WilliamD 14.9k●4●21 add new comment One Answer: oldestnewestmost voted 3 If you want to have conditional COUNTs applied to different columns you may want to use SUM() with a CASE statement embedded into it. The following should deliver the same result as your original query: SELECT order_status, SUM(CASE WHEN order_status NOT LIKE 'Provisioning-Order Eliminated' AND ORDER_STATUS NOT LIKE 'Provisioning-Order Completed' AND ORDER_STATUS NOT LIKE 'Provisioning-Order Returned' THEN 0 ELSE 1 END) FROM GDC GROUP BY order_status You can then take this and perform a similar SUM() with a CASE statement on the second column you want to count on. link answered 23 hours ago WilliamD 14.9k●4●21 HI William, Actually o dont need conditional count, what is required is having the count for 2 columns, with both having LOV. The script i provided has Order Status, there are some 15 items in it for which it is providing the count. The next i need is of these status for another LOV, i.e. NIMS_STATUS which has 2 items either issued or pending. Order Status Count Provisioning-Order Assigned 125 GAM-LL/XC delivery pending 54 CM-SD CNOC (On-net mapping) 46 PM-Closure confirmation Pending 30 PM-Details awaited 28 Cancellation Date awaited 21 Internal: Pending with Partner 14 PM-CNR 13 PM-Cust reference Awaited 13 Order to be Assigned 12 Internal: PIP-Port reservation 12 Provisioning-Order on Hold 10 Others/ MISC 9 Project Related Order 9 Site-XC Pending 8 PM-Partial hand-over/turn up pending 8 Site-Activation Pending 8 GAM-Hand-over docs pending 7 GAM-LL issue 5 Site-Testing 5 CM-IP config/ turn-up pending 3 Pending with Sales/ SE 3
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
@samar - please keep this in the context of the original question If you haven't yet got a successful answer, please use comments or edit your content to ask for more specifics
2 Likes 2 ·

0 Answers