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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query join
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrivero1961
Starting Member

7 Posts

Posted - 03/28/2013 :  16:06:18  Show Profile  Reply with Quote
Hi there, hope in your help.

In my DB I've two tables: TABLE_LONG and TABLE_SHORT.
The two tables are equal but in TABLE_LONG a recording long events and in TABLE_SHORT a recording short events.

If count number of long events, I've this output:

SELECT
	COALESCE (idDGIG, 'Tot') AS sGIG,
	`NUMBER`
FROM
	(
		SELECT
			LEFT (idDGIG, 2) AS idDGIG,
			COUNT(idDGIG) AS NUMBER
		FROM
			TABLE_LONG
		WHERE
			1
		AND (
			LEFT (idDGIG, 2) LIKE '%QM%'
			OR LEFT (idDGIG, 2) LIKE '%QI%'
			OR LEFT (idDGIG, 2) LIKE '%QO%'
			OR LEFT (idDGIG, 2) LIKE '%QS%'
		)
		AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
		GROUP BY
			LEFT (idDGIG, 2) WITH ROLLUP
	) AS Q;
+--------+--------+
| sGIG   | NUMBER |
+--------+--------+
| QI     |      9 |
| QM     |      2 |
| QO     |      6 |
| QS     |      5 |
| Tot    |     22 |
+--------+--------+
5 rows in set


If count number of short events, I've this output:

SELECT
	COALESCE (idDGIG, 'Tot') AS sGIG,
	NUMBER
FROM
	(
		SELECT
			LEFT (idDGIG, 2) AS sGIG,
			COUNT(idDGIG) AS NUMBER
		FROM
			TABLE_SHORT
		WHERE
			1
		AND (
			LEFT (idDGIG, 2) LIKE '%QM%'
			OR LEFT (idDGIG, 2) LIKE '%QI%'
			OR LEFT (idDGIG, 2) LIKE '%QO%'
			OR LEFT (idDGIG, 2) LIKE '%QS%'
		)
		AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
		GROUP BY
			LEFT (idDGIG, 2) WITH ROLLUP
	) AS Z;
+--------+-----------+
| sGIG   | NUMBER    |
+--------+-----------+
| QI     |         2 |
| QM     |         2 |
| QO     |        16 |
| QS     |         6 |
| Tot    |        26 |
+--------+-----------+
5 rows in set


Now I need tried join two tables with this query; I think in output total events ( long + short ): 22+26 = 48.
Instead I've this wrong output (1144), why?
Can you help me.
Thanks in advance.


SELECT
	DATE_START,
	COALESCE (idDGIG, 'Tot') AS sGIG,
	`NUMBER`
FROM
	(
		SELECT
			CA.DATE_START AS DATE_START,
			LEFT (CA.idDGIG, 2) AS sGIG,
			COUNT(CA.idDGIG) + COUNT(A.EVENT) AS NUMBER
		FROM
			TABLE_LONG CA
		JOIN TABLE_SHORT A ON CA.DATE_START = A.DATE_START
		WHERE
			CA.DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
		AND (
			LEFT (CA.idDGIG, 2) LIKE '%QM%'
			OR LEFT (CA.idDGIG, 2) LIKE '%QI%'
			OR LEFT (CA.idDGIG, 2) LIKE '%QO%'
			OR LEFT (CA.idDGIG, 2) LIKE '%QS%'
		)
		GROUP BY
			LEFT (CA.idDGIG, 2) WITH ROLLUP
	) AS SSS;
+-------------+--------+--------+
| DATE_START  | sGIG   | NUMBER |
+-------------+--------+--------+
| 2013-03-27  | QI     |    468 |
| 2013-03-27  | QM     |    104 |
| 2013-03-27  | QO     |    312 |
| 2013-03-27  | QS     |    260 |
| 2013-03-27  | Tot    |   1144 |
+-------------+--------+--------+
5 rows in set

Edited by - mrivero1961 on 03/28/2013 16:27:42

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/28/2013 :  16:10:21  Show Profile  Reply with Quote
Did you want a SQL Server version of that query or are you looking for MySQL help?

This is a SQL Server forum, so you'll probably get better/faster help on a forum for MySql.
Go to Top of Page
  Previous Topic Topic Next 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.06 seconds. Powered By: Snitz Forums 2000