Query with sub query - SQL Server Forums

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/

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

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]

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.

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

Oliviaf2012
Starting Member

16 Posts

Posted - 2012-10-03 : 09:13:32

Perfect - I found it.
Thanks a milion - excellent help :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 10:16:18

Glad to help & glad you got it figured out :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources