Problem in Query

Question

Post reply

Problem in Query

Ahmad Osama

SSCertifiable

Points: 7676
More actions
January 4, 2008 at 4:04 am

#377768

Hi All,
consider the below given snapshot
Category count sales
ABC 1 250
ZYX 2 450
PQR 3 550
I have got the above output by joining few tables.
What i wanna do is to add the Grand total as the last row
which displays total count and total sales.
some thing like below
Category count sales
ABC 1 250
ZYX 2 450
PQR 3 550
Total 6 1100
Pls help
Regards,
[font="Verdana"]Sqlfrenzy[/font]

Viewing 14 posts - 1 through 13 (of 13 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

2 things that come to my mind now...

you can either use a temp table, insert rows into it and then update the sum.

otherwise you can use a union query

select Category, count,sales from ....

union all

select 'total',sum(count),sum(sales) from .....

group by .....

"Keep Trying"

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 2

You can do like this:

select Category, Count, sales from 'Your Table view or embedded query'

union

select 'Total', sum(Count), sum(Sales) from 'Your Table view or embedded query'

order by 2

The result will be:

-------------------------

CategoryCountSales

-------------------------

ABC1250.00

ZYX2450.00

PQR3550.00

Total61250.00

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 3

[yourtable] is

Category ct sales

ABC 1 250

ZYX 2 450

PQR 3 550

select Category, sum(ct), sum(sales)

from [yourtable]

group by Category

with rollup

produces the desired results:

ABC1250

PQR3550

ZYX2450

NULL61250

read about the GROUPING() function to control field values in the total row.

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 4

Hi

Actualy I have used distinct im my query thus i can't use roll up

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 5

Hi chirag ,

Thanx for help.

I have used count and sum in my query to get category count and sales

Qry is somwwhat like this

select z.category,x.count(distinct customers),y.sum(sales) from

x inner join y on x.id=y.id

inner join z.ide=y.id

so how shud i use union to get the stated result.

how ever i have used a temp table to get the result.

Thanx

Regards,
[font="Verdana"]Sqlfrenzy[/font]

SqlUser-529296 SSCarpal Tunnel Points: 4072 More actions · Answer 6

Hi,

Try this one.This may helpful for you.

DROP TABLE #tem

DROP TABLE #temp1

CREATE TABLE #tem (Category char(3),cnt int,Sale int)

INSERT INTO #tem (Category,cnt,Sale)

SELECT 'ABC',11,450

UNION ALL

SELECT 'DEF',22,950

UNION ALL

SELECT 'GHI',3,690

SELECT * FROM #tem

CREATE TABLE #temp1 (Cate char(10),Ct int,Tsale int)

INSERT INTO #temp1

SELECT 'Total',SUM(cnt),SUM(Sale) FROM #tem

SELECT * FROM #tem

UNION ALL

SELECT * FROM #temp1

Regards,

SqlUser 🙂

R.P.Rozema SSChampion Points: 12317 More actions · Answer 7

If you give us the structure of your tables we'll be able to give you the query. You can get over the 'distinct' issue easily using sub-queries. i.e. you'll be able to use with rollup to give you your totals even though you've used a distinct. Besides, it's very likely you won't even need the distinct if you've properly built your database. And more importantly, it'll perform so much better that way.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 8

Ahmad,

I already gave you the answer. The keyword here that will solve your purpose is 'embedded query'

The solution will be:

---------------------------------------------------------

select z.category,x.count(distinct customers) as cnt, y.sum(sales) as sales from

x inner join y on x.id=y.id

inner join z.ide=y.id

union

select 'Total', sum (Q.cnt), sum(Q.sales) from

(select z.category,x.count(distinct customers) as cnt, y.sum(sales) as sales from

x inner join y on x.id=y.id

inner join z.ide=y.id) Q

order by 2

---------------------------------------------------------

But, please note that the above query will work only when your original query which I am using as the 'embedded query' works correctly.

select z.category,x.count(distinct customers),y.sum(sales) from
x inner join y on x.id=y.id
inner join z.ide=y.id

But the above query is not correct. It has both syntax and logical errors. You can never say x.count(distinct customers). It can be count(distinct x.customers)

I may help you if you provide me the structure of your three tables x, y, z

============================================

sqluser,

You are making things further clumsy.

Firstly, there is no need to use temp tables here when the result is easily available in one query.

Secondly, you are inserting constant values into #tem while it is coming from joinig 3 tables here in this case.

Sincerely,

Maz

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 9

Hi maz,

oops!! i made typing mistake...any ways thanx for the qry.

regards,

Ahmad

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 10

Hi Maz,

select z.category,x.count(distinct customers) as cnt, y.sum(sales) as sales from

x inner join y on x.id=y.id

inner join z.ide=y.id

union

select 'Total', sum (Q.cnt), sum(Q.sales) from

(select z.category,x.count(distinct customers) as cnt, y.sum(sales) as sales from

x inner join y on x.id=y.id

inner join z.ide=y.id) Q

order by 2

Query is correct but order by 2 is not working where as order by sales is

working...do u have an idea why is this happening.

regards,

Ahmad:)

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Nebojsa Ilic SSCommitted Points: 1735 More actions · Answer 11

Think about grouping with rollup. It's very useful option.

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 12

Sorry, no idea. You should provide the structure of your three tables x, y, z and some sample rows if you want more help.

Sincerely,

Maz

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

Ahmad Osama (1/7/2008)
Hi
Actualy I have used distinct im my query thus i can't use roll up

Change it... it's worth it in terms of performance. 😉

--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)