Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Query with sub query

Author  Topic 

Oliviaf2012
Starting Member

16 Posts

Posted - 2012-10-01 : 12:38:34
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

52326 Posts

Posted - 2012-10-01 : 12:51:38
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 - 2012-10-02 : 04:33:45
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-02 : 07:26:45
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]
Go to Top of Page

Oliviaf2012
Starting Member

16 Posts

Posted - 2012-10-03 : 05:14:50
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 06:37:27
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 - 2012-10-03 : 09:13:32
Perfect - I found it.
Thanks a milion - excellent help :)
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 10:16:18
Glad to help & glad you got it figured out :)
Go to Top of Page
   

- Advertisement -