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 2008 Forums
 Transact-SQL (2008)
 Query join
 New Topic  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

4354 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  
 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.03 seconds. Powered By: Snitz Forums 2000