January 4, 2008 at 4:04 am
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]
January 4, 2008 at 4:32 am
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"
January 4, 2008 at 4:45 am
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:
-------------------------
Category Count Sales
-------------------------
ABC 1 250.00
ZYX 2 450.00
PQR 3 550.00
Total 6 1250.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]
January 4, 2008 at 8:47 am
[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:
ABC 1 250
PQR 3 550
ZYX 2 450
NULL 6 1250
read about the GROUPING() function to control field values in the total row.
January 7, 2008 at 2:53 am
Hi
Actualy I have used distinct im my query thus i can't use roll up
Regards,
[font="Verdana"]Sqlfrenzy[/font]
January 7, 2008 at 3:02 am
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]
January 7, 2008 at 3:50 am
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 🙂
January 7, 2008 at 5:49 am
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.
January 7, 2008 at 11:35 am
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]
January 8, 2008 at 1:17 am
Hi maz,
oops!! i made typing mistake...any ways thanx for the qry.
regards,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
January 8, 2008 at 1:38 am
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]
January 8, 2008 at 1:53 am
Think about grouping with rollup. It's very useful option.
January 8, 2008 at 7:55 am
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]
January 8, 2008 at 7:58 pm
Ahmad Osama (1/7/2008)
HiActualy 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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply