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

Transact-SQL (2008)

Query join

Author

Topic

mrivero1961
Starting Member

7 Posts

Posted - 2013-03-28 : 16:06:18

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-28 : 16:10:21

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources