SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Query with sub query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Oliviaf2012
Starting Member

16 Posts

Posted - 10/01/2012 :  12:38:34  Show Profile  Reply with Quote
Can a group by and order by be done on a query like this
to retun this data for all countries in the same query not just 1 (ie removing the Where country = )


SELECT sum(extendedamount) as UK
FROM AllActiveAuthorised AS AA
WHERE l2g_dispatcheddate >= CONVERT(DATETIME, '2012-09-01 00:00:00', 102)
AND l2g_dispatcheddate <= CONVERT(DATETIME, '2012-10-01 00:00:00', 102)
AND EXISTS
(SELECT address2_fax FROM AllActiveAuthorised WHERE address2_fax = AA.address2_fax
AND l2g_dispatcheddate < CONVERT(DATETIME, '2012-09-01 00:00:00', 102)
AND [Country Code] = 'GB'
AND allowmail = 'Allow'
and statuscode = '1'
AND l2g_brandname = 'Y' )
AND
[Country Code] = ('GB')
AND allowmail = 'Allow'
and statuscode = '1'
AND l2g_brandname = 'Y'

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/01/2012 :  12:51:38  Show Profile  Reply with Quote
you can apply GROUP BY and ORDER BY to this

But to give you correct solution we need to understand what you're trying to achieve

can you some sample data and show sample output of what you want out of them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Oliviaf2012
Starting Member

16 Posts

Posted - 10/02/2012 :  04:33:45  Show Profile  Reply with Quote
Hi
I am trying to show the revenue generated this month by customers that had purchased from us previously. SO my results should look like this

UK - 2560.00
IE - 1200.50
FR - 4561.13
US - 7851.23
etc
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/02/2012 :  07:26:45  Show Profile  Reply with Quote
Do it like in the query below -see red highlights. I made a few other changes in addition to those shown in red, but none of them should change the outcome.

One thing I noticed is that you are joining on address2_fax (which I assume is a fax number or address). It would be better to join on the primary key column(s).
SELECT AA.[Country Code],
       SUM(extendedamount)  AS Amount
FROM   AllActiveAuthorised  AS AA
WHERE  l2g_dispatcheddate >= CAST('20120901' as DATETIME)
       AND l2g_dispatcheddate < CAST('20121001' as DATETIME)
       AND EXISTS 
           (
               SELECT *
               FROM   AllActiveAuthorised AB
               WHERE  AB.address2_fax = AA.address2_fax
                      AND AB.l2g_dispatcheddate < CAST('20120901' as DATETIME)
                      AND AB.[Country Code] = AA.[Country Code]
                          --AND [Country Code] = 'GB'
                      AND AB.allowmail = 'Allow'
                      AND AB.statuscode = '1'
                      AND AB.l2g_brandname = 'Y'
           )
           --AND [Country Code] = ('GB')
       AND allowmail = 'Allow'
       AND statuscode = '1'
       AND l2g_brandname = 'Y'
GROUP BY
       [Country Code]
ORDER BY
       [Country Code]

Edited by - sunitabeck on 10/02/2012 07:28:19
Go to Top of Page

Oliviaf2012
Starting Member

16 Posts

Posted - 10/03/2012 :  05:14:50  Show Profile  Reply with Quote
Hi
I tried this but it returned no data. The address2_fax field is actually the primary key it was just not renamed.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/03/2012 :  06:37:27  Show Profile  Reply with Quote
quote:
Originally posted by Oliviaf2012

Hi
I tried this but it returned no data. The address2_fax field is actually the primary key it was just not renamed.

Add a TOP N clause, and remove the where clauses one at a time to see which of those are causing all the data to be filtered out.
SELECT TOP 10 AA.[Country Code],
       SUM(extendedamount)  AS Amount
....
Go to Top of Page

Oliviaf2012
Starting Member

16 Posts

Posted - 10/03/2012 :  09:13:32  Show Profile  Reply with Quote
Perfect - I found it.
Thanks a milion - excellent help :)
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/03/2012 :  10:16:18  Show Profile  Reply with Quote
Glad to help & glad you got it figured out :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000