Count/ Group by with multiple outputs

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 2008 Forums

Transact-SQL (2008)

Count/ Group by with multiple outputs

Author

Topic

Trulla
Starting Member

4 Posts

Posted - 2012-02-15 : 04:36:00

Hey there

I try to find the customer value in a database:

customer_email created_at status

a@a.com 12.1.10 complete
b@a.com 14.1.10 cancelled
c@a.com 16.1.10 complete
a@a.com 18.1.10 complete
d@a.com 18.1.10 complete
c@a.com 20.1.10 complete

Now I want that i shows me all the customers (identified by email) and when did they order. If I put

SELECT customer_email, created_at
FROM *
WHERE 'status' = 'complete'
Group by customer_email

It only shows me the first created_at date

Is there a way it shows me all the date in more than one row?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-15 : 04:44:24

quote:
Originally posted by Trulla

SELECT customer_email, created_at
FROM *
WHERE 'status' = 'complete'
Group by customer_email

It only shows me the first created_at date

This would not even run.

Is there a way it shows me all the date in more than one row?
Not sure what you want here, maybe this ?


select customer_email, created_at
from   yourtable
where  status = 'complete'

KH
[spoiler]Time is always against us[/spoiler]

Trulla
Starting Member

4 Posts

Posted - 2012-02-15 : 07:33:52

Thank you but
It does run, but I wana see the email adresses group, not that I have to search for all the emails in a database with over one year of clients :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-15 : 09:28:32

quote:
Originally posted by Trulla

Thank you but
It does run, but I wana see the email adresses group, not that I have to search for all the emails in a database with over one year of clients :)

whatever you're asking for doesnt make much sense to us. Please put some sample data for us and clearly show what you want as output
Reading first post, what you're asking looks like you want to group based on customer email. But didnt understand how you'll be able to show all dates related to customer against that single row. or is it like you want them as a csv list aginst email?

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

Trulla
Starting Member

4 Posts

Posted - 2012-02-15 : 10:47:18

Thanks for the reply. Sorry for being unprezise:

What I want as output is:

Email adress: and then all the dates the customer did buy.

so for a@a.com i want to see 12.1 and 18.1. I know a@a.com is only a single row but how can i put a second row there? Or what would be helpful already is a all the a@a.com after each other and all the dates and then I can create a lag in Excel.

My system identifies people by email adress. In the end I wish to see how many times a customer buys in a year and what is the lag between the two dates.

Thanks so much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-15 : 14:59:12

this is one way of getting all dates in same row


SELECT customer_email,
STUFF((SELECT ',' + created_at FROM table WHERE customer_email = t.customer_email ORDER BY created_at FOR XML PATH('')),1,1,'') AS datelist
FROM (SELECT DISTINCT customer_email FROM table)t

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 15:58:43

Are you working with SQL Server or some other platform?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Trulla
Starting Member

4 Posts

Posted - 2012-02-16 : 03:19:26

Thanks sooo much :)

I tried it and somehow its not working...i replaced table with the name = eshop_sales_flat_order

The error its coming out is:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'XML PATH('')),1,1,'') AS datelist FROM (SELECT DISTINCT customer_email FROM esho' at line 2

I am just started to use this database so I am sorry if the questions are dull :/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-16 : 03:25:48

quote:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'XML PATH('')),1,1,'') AS datelist FROM (SELECT DISTINCT customer_email FROM esho' at line 2

it seems that you are not using Microsoft SQL Server. SQLTeam.com is on MS SQL Server not MySQL

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources