Query for summerised report

Question

Query for summerised report

sskumar ede

Newbie

Points: 3
More actions
August 5, 2009 at 6:51 am

#213122

Hi all,
I have a table with columns softwares,version,licenseno,status(installed/uninstalled). I would like to display the result as
softwaresversiontotalinstalleduninstalled
MSOffice XP 150 100 50
Can any one please reply how can i achieve this....
thanks
kumar.

Viewing 5 posts - 1 through 5 (of 5 total)

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

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 1

We'd like to see you try. This isn't consulting where we do the work, but rather where we help.

If you need to summarize data, you can use COUNT() or SUM(), depending on what you are trying to do. Read about them in books online.

Basically you'll run a select with the software column and the sum or count on the other columns, and add a GROUP BY in your query for the software.

sskumar ede Newbie Points: 3 More actions · Answer 2

Thanks for the reply

I tried the bellow queries

1) To get total product count

"select product,versionnumber,count(*) from net_productinfo group by product,versionnumber order by product"

2) To get availabile products

"select product,versionnumber,availability,count(*) from net_productinfo where availability like 'A' group by product,versionnumber,availability order by product"

3) To get Unavailable products

"select product,versionnumber,availability,count(*) from net_productinfo where availability like 'N' group by product,versionnumber,availability order by product"

My intension is to get the result of all three queries in a single Row like " product,versionnumber,total,available,unavailable "

Please provide guidelines for me

Thanks,

kumar.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

It's called a "Cross Tab"...

SELECT Product,

VersionNumber,

COUNT(*) AS Total,

SUM(CASE WHEN Availability = 'A' THEN 1 ELSE 0 END) AS Available,

SUM(CASE WHEN Availability = 'N' THEN 1 ELSE 0 END) AS UnAvailable

FROM dbo.Net_ProductInfo

GROUP BY Product, VersionNumber

ORDER BY Product, VersionNumber

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sskumar ede Newbie Points: 3 More actions · Answer 4

Excellent.........

It is working

Thanks,

have a good day,

kumar