Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
My below query return store_name, Product, count of each product as well count of total product under one account.
I want to add one column for store code, but i couldn't.


Output look like below :

Store_name Store_code Product count
Store_1      100               5 
                       abc123  2
                       abc456  3
Store_2      102               7 
                       bcd123  4
                       bcd456  3


What I have tried:

select case 
                when pt.name is null and a.name is not null then a.Name
                when pt.name is null and a.name is null then 'Grand Total'
                else ''
                end store_name,
                                             
pt.name SIC, count(*) total
from prod p, product_type pt, account a, product_status ps
where p.activation_date >= to_date ( '20/SEP/2018 00:00:00', 'DD/MON/YYYY HH24:MI:SS') 
and p.prod_type in (select id from product_type where prod_id in (123,456,789)
and a.id = p.owner
and p.status = ps.id
and pt.id = p.prod_type
group by rollup  (a.Name, pt.name)order by a.name, pt.name desc
Posted
Updated 25-Sep-19 21:13pm
v7
Comments
W Balboos, GHB 25-Sep-19 10:42am    
And how are any of us supposed to know how to get your store code when, at the very least, we don't know how your data is stored?

That, and from what you have tried, you don't ask for it, wherever it is.
RedDk 25-Sep-19 13:58pm    
Null or NA or blank in the return from a SELECT is tricky without a post-process of some kind. Which is usually done in a higher code layer than the TSQL. I'd say redesign your table so that SELECT contains a JOIN ... which is masking the fact that instead of one table from which the structure is being queried, there are two tables or more.

This is basic TSQL. Might have a look at AdventureWorks samples too.
ZurdoDev 26-Sep-19 11:57am    
What??

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900